CODEWITHSUNDEEP
pythondjangopostgresqldatetime
qarthandso
qarthandso

Reputation: 2190

Django/Python and Raw SQL Querying with PostgreSQL

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?

Upvotes: 0

Views: 1216

Answers (1)

fahyik yong
fahyik yong

Reputation: 141

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.

Upvotes: 1

Related Questions