How to use proper data type in SQL?

Question

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

Does this answer your question? [Inserting datetime into database using pyscopg2 and PostgreSQL](https://stackoverflow.com/questions/30243530/inserting-datetime-into-database-using-pyscopg2-and-postgresql) — Olvin Roght, Jul 31 '22 at 15:09

score 0 · Accepted Answer · answered Jul 31 '22 at 15:10

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)})

How to use proper data type in SQL?

1 Answers1