3

In my python application I run a query which returns me a product from my product table. When I get the result and assign it in a variable, it is stored in a list. But I want to create a product class and want to store query result in an object of my product class which matches with my database table.

But I dont want to do it in this way which is one by one getting values from list and setting object variables and so on.

I want to do this because my database table is large and list does not store variable's field names. It comes like following; so I cant use something like this; getProductName()

[5,"strawberry",011023234,10.5]

so I should remember in which position my product barcode code is stored when it is needed.

Retrieve data from database;

vt = sqlite3.connect("vt.sqlite")
im = vt.cursor()
im.execute("SELECT * FROM Products WHERE barcode='{}'".format(barcode))
veriler = im.fetchall()

Here is veriler = [5,"starberry",001].

My database table will be large. Then my veriler will be a long list.

When I want to get some attribute of retrieved data I have to access it like veriler[0], veriler[25] etc. I should remember indexes for each attribute.

Is it possible if I had product class which has all attributes in my database table and create a product object and when I assigned the database result to my product object I have set all attributes in product object and can access attributes of my product object using get methods.

Thanks in advance.

Hilal
  • 902
  • 2
  • 22
  • 47
  • Please include the code you are using to retrieve the data from the database. Also could you be more precise on what the expected result is? – Matthias Wiehl Aug 10 '16 at 09:32
  • Sounds like you might want an [ORM](https://www.fullstackpython.com/object-relational-mappers-orms.html). – glibdud Aug 10 '16 at 12:46
  • That is really nice, but seems ORM is more than what I want. I only need to convert result to an object simply. – Hilal Aug 10 '16 at 13:12
  • In general, for such purposes, I recommend using an ORM. The most popular is SQLAlchemy, but if you need something small and easy to use you can consider peewee. It's extremely helpful when you need to use databases in python. – warownia1 Aug 10 '16 at 21:16
  • https://stackoverflow.com/questions/13227142/using-row-to-json-with-nested-joins#:~:text=225,%3D%20ur.duty_id%3B this answer saved my life! – Hila Grossbard Dec 03 '22 at 22:38

4 Answers4

1

The pscycopg2 NamedTupleCursor provides objects for database results.

gerardw
  • 5,822
  • 46
  • 39
1

sqlite3 has a built in method to do this:

https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Row

A Row instance serves as a highly optimized row_factory for Connection objects. It tries to mimic a tuple in most of its features.

It supports mapping access by column name and index, iteration, representation, equality testing and len().

If two Row objects have exactly the same columns and their members are equal, they compare equal.

keys()

This method returns a list of column names. Immediately after a query, it is the first member of each tuple in Cursor.description.

So in your case:


vt = sqlite3.connect("vt.sqlite")
vt.row_factory = sqlite3.Row

Or you can build your own:

https://docs.python.org/3.8/library/sqlite3.html#connection-objects

row_factory

You can change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row. This way, you can implement more advanced ways of returning results, such as returning an object that can also access columns by name.

Example:

import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])

con.close()
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
0

Okay I think found out how to do it with some more searching.

Not sure it is the best way but it worked for me.

Convert database result into json and than convert json to python object.

Community
  • 1
  • 1
Hilal
  • 902
  • 2
  • 22
  • 47
0

When converting code from sqlite3 to psychopg3 I also encountered this problem (access via index instead of column name). I found the concept of RowFactories quite useful.

Apparently, in sqlite3 it is the default to access fields via the column name, but in psychopg3 only via the index. It should be noted that index = 0 represents the number of the data record and not the first field.

Example:

{0:0, : 1: 'John Doe', 2: 'age'}

If you give the connection (or the cursor) an additional parameter "row_factory", then column names are correctly set as key names.

Example:

from psycopg.rows import dict_row
...
cur = connection.cursor(row_factory=dict_row)

{'name': 'John Doe', 'age': 33}

Python objects can also be created there (@dataclass/class_row). If the built-in row factories are not sufficient, it is possible to provide your own row factory implementations.

07713
  • 36
  • 3