CODEWITHSUNDEEP
pythonpostgresqldatetime
user5893028
user5893028

Reputation:

How send Datetime as parameter in SQL command on Python?

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?

Upvotes: 2

Views: 6081

Answers (1)

donkopotamus
donkopotamus

Reputation: 23206

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.

Upvotes: 2

Related Questions