How send Datetime as parameter in SQL command on Python?

Question

I have a sql command and just want to select some records with some conditions ( using python & db is Postres): So, my query is:

 current_date= datetime.now()

  tt = yield self.db.execute(self.db.execute('SELECT "Id", "RubricId", "IsRubric"  
     FROM whis2011."CoockieUserInterests"
     'WHERE "UserId" = %s AND "Date" = %s '
     % (Id, current_date))

result=tt.fetchall()[0]

Problem: when I want to pass datetime to field "Date" I got error:

syntax error at or near "00"
LINE 1: ...rests" WHERE "UserId" = 1 AND "Date" = 2016-10-05 00:22:07.3...
                                                         ^

All "Date" fields in db is like: 2016-09-25 00:00:00

Also, datatype of field "Date" in database is "timestamp without time zone".

it's my pool:

    application.db = momoko.Pool(
    dsn='dbname=xxxx user=xxxxx password=xxxxx host=xxxx port=5432',
    size=1,
    ioloop=ioloop,
)

How I can select "Date" with format like this in my db?

You are vulnerable to sql injection attacks. You need to read this q/a: stackoverflow.com/questions/10950362/… — Marc B, Oct 4, 2016 at 21:43

donkopotamus · Accepted Answer · 2016-10-04 22:03:43Z

2

You don't state what module you are using to connect to postgresql. Let's assume for the interim that it is psycopg2.

In that case, you use the following to pass parameters to a query:

current_date = datetime.now()

self.db.execute(
    'SELECT Id, RubricId, IsRubric '
    'FROM whis2011.CoockieUserInterests '
    'WHERE UserId = %s AND Date = %s',
     (Id, current_date))

Note we are not using the % interpolation operator on the sql string here. Instead we are using %s to mark sql parameters and then passing them separately to cursor.execute

If you are using a different package to connect to Postgres then it may mark parameters in a different manner. Check the documentation or the modules paramstyle for details.

edited Oct 4, 2016 at 22:03

answered Oct 4, 2016 at 21:54

donkopotamus

22k2 gold badges47 silver badges59 bronze badges

I'm using "momoko", I don't have curser!
– user5893028
Oct 4, 2016 at 22:04
Surely even a cursory read of the documentation would have told you that momoko is a wrapper around psycopg2, and that a connection.execute method takes parameters just like a cursor.execute does?
– donkopotamus
Oct 4, 2016 at 22:17
I changed but still error, I updated code with my connection pool maybe it's useful to see what happened in my code!
– user5893028
Oct 4, 2016 at 22:58
You get the same error? Your original error is because you did not enclose quotes. But this parameterized answer does not need quotes. Please advise.
– Parfait
Oct 5, 2016 at 0:04

Add a comment |

Collectives™ on Stack Overflow

How send Datetime as parameter in SQL command on Python?

1 Answer 1

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related