How to use proper data type in SQL?

byMR

July 31, 2022

I’m working with dvdrental database and I want to limit the rental table by choosing a specific rental_date.

When i print content of rental_date I receive a table like:

[(datetime.datetime(2005, 5, 24, 22, 54, 33),), (datetime.datetime(2005, 5, 24, 23, 3, 39),), …

I tried to limit my rental table by using code like this:

import psycopg2 as pg

conn = pg.connect(host='localhost', port='5432', dbname='dvdrental', user='postgres', password='123')
    
cur = conn.cursor() 
    
cur.execute("SELECT * FROM rental WHERE rental_date=datetime.datetime(2005, 5, 24, 22, 54, 33),")

The result of that code is syntax error. After removing comma I got error:

InvalidSchemaName: schema "datetime" does not exist

Is there any possibility to get access to that datetime.datetime data type in my cur.execute command?

Thank you for your answers 🙂

I’m using Python 3.9.4 and psycopg2 2.9.3

>Solution :

Generally if you want to pass arguments from your language of choice to SQL, you want a parametrized query.

In the case of psycopg2 – There’s some useful documentation with examples.
https://www.psycopg.org/psycopg3/docs/basic/params.html#execute-arguments

Here’s a simple one that should get you started

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

Note that they use the %s and then pass a tuple of values to the execute function, each one is used in place.

You can also use named arguments

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})