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](http://bobby-tables.com). You need to read this q/a: http://stackoverflow.com/questions/10950362/protecting-against-sql-injection-in-python — Marc B, Oct 04 '16 at 21:43

donkopotamus · Accepted Answer · 2016-10-04T22:03:43.540

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 04 '16 at 22:03

answered Oct 04 '16 at 21:54

donkopotamus

22,114
2
48
60

I'm using "momoko", I don't have curser! – Oct 04 '16 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`](http://momoko.61924.nl/en/latest/api.html#momoko.Connection.execute) method takes parameters just like a `cursor.execute` does? – donkopotamus Oct 04 '16 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! – Oct 04 '16 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 05 '16 at 00:04

How send Datetime as parameter in SQL command on Python?

1 Answers1