Django/Python and Raw SQL Querying with PostgreSQL

Question

I'm practicing my raw SQL querying in a Django project using cursor.execute.

Here's my Django models.py database schema:

class Client(models.Model):
    date_incorporated = models.DateTimeField(default=timezone.now)

And here's the psql description of the table:

# \d+ client

      Column       |           Type           |      Modifiers     | Storage  |
-------------------+--------------------------+--------------------+----------+
 date_incorporated | timestamp with time zone | not null           | plain    |

Here's where I get confused:

If I use psql to query the data from the table, I get:

# SELECT date_incorporated FROM client;

   date_incorporated    
------------------------
 2017-06-14 19:42:15-04
 2017-11-02 19:42:33-04
(2 rows)

This makes sense to me. In the PostgreSQL docs, it shows that this is (I believe) just a string that is correctly formatted and stored as a UTC timestamp.

When I go through Django using this query:

cursor.execute('SELECT date_incorporated FROM client;')
data = [dict(zip(columns,row)) for row in cursor.fetchall()]

(using the dictfetchall method from the Django docs)

...my date_incorporated field gets turned into a python datetime object.

{'date_incorporated': datetime.datetime(2017, 11, 2, 23, 42, 33, tzinfo=<UTC>)}

In this app I'm building, I wanted a user to be able to input raw SQL, and put that inputted string into the cursor.execute(rawSQL) function to be executed. I expected the output to be the same as the psql version.

If I was using the Django ORM, I might've expected the timestamp with time zone to be converted to a time-zone aware datetime object, but since I'm doing a raw SQL call, I expected to get back 2017-06-14 19:42:15-04, not a python datetime object.

Is the fetchall method still acting as the Django ORM and converting certain fields?

fahyik yong · Accepted Answer · 2017-11-29 01:28:39Z

1

I believe this is standard conversion from using any interface driver. You would get the same result even if you use py-postgressql, i.e. the cursor is doing the conversion according to the field type defined in the database.

Long story short, the dictfetchall is not doing any conversion, but rather parsing the converted result from the cursor.

answered Nov 29, 2017 at 1:28

fahyik yong

1416 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qarthandso Over a year ago

Thank you. And the cursor is standard across all interface drivers you're saying? Most likely from PEP 249?

Collectives™ on Stack Overflow

Django/Python and Raw SQL Querying with PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related