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 — Olvin Roght
– Olvin Roght, Commented Jul 31, 2022 at 15:09

ConstantineK · Accepted Answer · 2022-07-31 15:10:19Z

0

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

answered Jul 31, 2022 at 15:10

ConstantineK

3625 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

How to use proper data type in SQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related