Error in Tutorial

Anvil Q&A
1

What I’m trying to do:
I am following this link :

What I’ve tried and what’s not working:

Code Sample:

(env) (base) girish@girish-System-Product-Name:~/myapps1$ cat inventory.py 
import psycopg2

conn = psycopg2.connect(
  host="18.133.244.120",
  user="xxxxxx",    <--- Here I have given correct user name as shown on the page
  password="yyyyyyyyy")   <--- Here I have given correct user name as shown on the page
conn.set_session(autocommit=True)

cur = conn.cursor()

def get_items():
  cur.execute('SELECT (id, item_name, quantity) FROM inventory;')
  items = cur.fetchall()
  return [
    {'id': item[0], 'name': item[1], 'quantity': item[2]}
    for item in items
  ]

print(get_items())
(env) (base) girish@girish-System-Product-Name:~/myapps1$ python3 inventory.py
Traceback (most recent call last):
  File "inventory.py", line 19, in <module>
    print(get_items())
  File "inventory.py", line 14, in get_items
    return [
  File "inventory.py", line 15, in <listcomp>
    {'id': item[0], 'name': item[1], 'quantity': item[2]}
IndexError: tuple index out of range
(env) (base) girish@girish-System-Product-Name:~/myapps1$ psql -h 18.133.244.120 -U xxxxxxxx   <--- Here I have given correct user name as shown on the page
Password for user xxxxxxx:  <--- Here I have given correct user name as shown on the page
psql (13.4 (Ubuntu 13.4-0ubuntu0.21.04.1), server 12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

obvious_neat_self=> select * from inventory;
 id |   item_name    | quantity 
----+----------------+----------
  1 | Vase           |       10
  2 | Bookcase       |        5
  3 | Bathtowel      |       20
  4 | Frying Pan     |       20
  5 | Large Saucepan |       25
  6 | Small Saucepan |       25
  7 | Dinner Plate   |       15
  8 | Dining Chair   |       10
(8 rows)

obvious_neat_self=> \q
(env) (base) girish@girish-System-Product-Name:~/myapps1$

But I am getting python error as shown above. Kindly help me how do I correct it. Is it a tutorial bug?

2

Try adding a print(list(items)) right after assigning items value, just to make sure you get a list of 3-tuples.

3

(env) (base) girish@girish-System-Product-Name:~/myapps1$ python3 inventory.py
[(’(1,Vase,10)’,), (’(2,Bookcase,5)’,), (’(3,Bathtowel,20)’,), (’(4,“Frying Pan”,20)’,), (’(5,“Large Saucepan”,25)’,), (’(6,“Small Saucepan”,25)’,), (’(7,“Dinner Plate”,15)’,), (’(8,“Dining Chair”,10)’,)]
Traceback (most recent call last):
File “inventory.py”, line 20, in
print(get_items())
File “inventory.py”, line 15, in get_items
return [
File “inventory.py”, line 16, in
{‘id’: item[0], ‘name’: item[1], ‘quantity’: item[2]}
IndexError: tuple index out of range
(env) (base) girish@girish-System-Product-Name:~/myapps1$ cat inventory.py
import psycopg2

conn = psycopg2.connect(
host=“xx.xx.xx.xx”,
user=“xxxxx”,
password=“xxxxx”)
conn.set_session(autocommit=True)

cur = conn.cursor()

def get_items():
cur.execute(‘SELECT (id, item_name, quantity) FROM inventory;’)
items = cur.fetchall()
print(list(items))
return [
{‘id’: item[0], ‘name’: item[1], ‘quantity’: item[2]}
for item in items
]

print(get_items())
(env) (base) girish@girish-System-Product-Name:~/myapps1$

4

Here you can see that each row is returned as a tuple containing a string representation of the 3 values, not as a tuple of values. This is the first row:
(’(1,Vase,10)’,)

You could get the first (and only) item of the tuple, convert it into 3 values and assign the 3 values to your variables.

I haven’t worked with psycopg2 in a while, but if I remember it is possible to configure it to get a tuple of values or a dictionary of values with the column names as keys.

I’m on my cell now, I could look at an old app and let you know what I’m doing later.

EDIT
Here is how to change how rows are returned: psycopg2.extras – Miscellaneous goodies for Psycopg 2 — Psycopg 2.9.1 documentation

5

Can you please try at your own and tell me how you solved the problem.

6

Any update please? I am trying to learn the things please.

7

This not generally a fair expectation when you are asking volunteers on a forum for help. In my understanding, you were given a suggestion to try, so why don’t you try it and report back. It would also be helpful to better format your posts (formatting the python code, and separating python code from other output) and/or share a clone link.

8

Try omitting the parentheses.

9

By constructing your query with the parentheses around the column names, PostgreSQL created an iterable of tuples that only contain one object when you used cursor.fetchall()

However, @hugetim is right, you should really try it yourself if you want to learn.

You also asked “How I solved the problem”, I looked at your code, and looked at the tutorial, and there was only one difference:

cur.execute('SELECT * FROM inventory;')
vs
cur.execute('SELECT (id, item_name, quantity) FROM inventory;')

Only because I work with raw SQL all day did that pique my interest.

1 Like
10

Is the upshot that there is a mistake in the tutorial, or not? A puzzling thing as I look at it now is that the tutorial uses 'SELECT * FROM inventory;' when first presenting the code, but then changes when it shows the script all together.

11

Ahh, you are right, I didn’t read the full tutorial, because I never did it.

It looks like there is a problem with the tutorial, the parentheses in the SQL query string should be omitted if the rest of the python code is going to stay the same, where it states:

image
image745×610 22.8 KB

Here is an example of PostgreSQL behavior using this postgreSQL website you can use to interactively try out PostgreSQL queries :

postgresqlexample

1 Like
12

Also, I understand the tutorial is for beginners, however the following code is more advanced, but might be a bit more pythonic:

You would use this in a situation as laid out in the tutorial, where you only expect to get a small number (less than 10000 for example) rows.

import psycopg2
import psycopg2.extras

conn = psycopg2.connect(
  host="18.133.244.120",
  user="your_database_id",
  password="your_database_password")
conn.set_session(autocommit=True)


def get_items():
  # using context management to open and close the cursor.
  # Changing how the cursor behaves to make it an iterable of dict-like row objects 
  with conn.cursor( cursor_factory = psycopg2.extras.RealDictCursor ) as cur:
    cur.execute('SELECT id, item_name, quantity FROM inventory;')

    return [dict(x) for x in cur]

print(get_items())
1 Like
13

Since you are using the RealDictCursor you should be able to simply return cur.fetchall() and get a list of dictionaries (I have not tried)

14

I agree, you should, and I believe it is supported by psycopg2. The thing is the same python implementation of row_factory or cursor_factory to turn the results into a list of dicts across other SQLs like MySQL, MsSQL and SQLite do not all do it the same way.

For example using import sqlite3 it supports the same concept, but you have to set it on the cursor or db object like cur.row_factory = sqlite3.Row and then it returns “dict-like” row objects that are still considered sqlite3 row objects.

What this means in practice is depending on implementation, the row objects may still be attached to the cursor (that could then be closed before you use the data), which may be attached to a database, which may be in-turn attached to a connection that is held open or could drop, time-out etc…
Also the cursor (at least for psycopg2 ) is not thread safe.
Forcing your rows to become python objects eliminates the issue, if what you are doing is returning data, and not processing it ‘on-the-fly’ as it comes in and letting the cursor close after you have what you need.

Regarding ‘Thread-safety’; I use the anvil data tables for just this reason, it seems like they have written the code with thread-safety in mind, and I can sleep at night because of that. :grinning:

1 Like