Thread: Fwd: Timestamp Query Parameters
Hi,
I'm just starting out with psycopg2. I'm trying to pass a timestamp query parameter to an sql query. Unfortunately the constructed query returns nothing, even though it should. The code follows; does anyone see what I'm doing wrong?date = datetime.now().strftime("%Y-%m-%d")
params = [datetime.strptime(date + " 00:00:00", '%Y-%m-%d %H:%M:%S'), datetime.s
trptime(date + " 23:59:59", '%Y-%m-%d %H:%M:%S')]
print params
try:
query = """select * from timesheet
s where submitted=true and started_at between '%s' and '%s'"""
cursor.mogrify(query, params)
On 03/13/2014 02:40 PM, Ari King wrote:
> Hi,
>
> I'm just starting out with psycopg2. I'm trying to pass a timestamp
> query parameter to an sql query. Unfortunately the constructed query
> returns nothing, even though it should. The code follows; does anyone
> see what I'm doing wrong?
>
> date = datetime.now().strftime("%Y-%m-%d")
> params = [datetime.strptime(date + " 00:00:00", '%Y-%m-%d %H:%M:%S'),
> datetime.s
> trptime(date + " 23:59:59", '%Y-%m-%d %H:%M:%S')]
> print params
> try:
> query = """select * from timesheet
> s where submitted=true and started_at between '%s' and '%s'"""
> cursor.mogrify(query, params)
Do not quote the %s.
>
> Thanks.
>
> -Ari
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 03/13/2014 03:11 PM, Ari King wrote:
> Do not quote the %s.
>
>
> Thanks for the suggestion, but unquoting the %s did not solve the issue.
Probably should have mentioned before:
mogrify(operation[, parameters])
Return a query string after arguments binding. The string returned
is exactly the one that would be sent to the database running the
execute() method or similar.
So mogrify does not actually run against the database. I was assuming
you where doing an execute() later which may be an erroneous assumption.
To use parameters in execute() see here:
http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
>
> -Ari
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 03/13/2014 03:30 PM, Ari King wrote: > Probably should have mentioned before: > > mogrify(operation[, parameters]) > > Return a query string after arguments binding. The string > returned is exactly the one that would be sent to the database > running the execute() method or similar. > > So mogrify does not actually run against the database. I was > assuming you where doing an execute() later which may be an > erroneous assumption. To use parameters in execute() see here: > > http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries > > > Thanks for the clarification. I was successful in using mogrify wrapped > in execute. Passing parameters to SQL queries via execute still won't > work for me though. Thoughts? Crank up the statement logging in postgresql.conf to 'all' and see what is actually hitting the database. > > -Ari > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, 13 Mar 2014 17:40:52 -0400
Ari King <ari.brandeis.king@gmail.com> wrote:
> Hi,
>
> I'm just starting out with psycopg2. I'm trying to pass a timestamp query
> parameter to an sql query. Unfortunately the constructed query returns
> nothing, even though it should. The code follows; does anyone see what I'm
> doing wrong?
>
> date = datetime.now().strftime("%Y-%m-%d")
> params = [datetime.strptime(date + " 00:00:00", '%Y-%m-%d %H:%M:%S'),
> datetime.s
> trptime(date + " 23:59:59", '%Y-%m-%d %H:%M:%S')]
> print params
> try:
> query = """select * from timesheet
> s where submitted=true and started_at between '%s' and '%s'"""
> cursor.mogrify(query, params)
Found this in the doc:
http://initd.org/psycopg/docs/usage.html#index-9
Looks like you can use Python Date/time objects directly.
Regards
--
Lutz Steinborn
4c Business Service GmbH
Putzbrunner Str. 71
81739 Muenchen
l.steinborn@4c-ag.de, http://www.4c-gmbh.de
---------------------------------------------------------
Sitz der Gesellschaft: Putzbrunner Str. 71, 81739 Muenchen
Vertretungsberechtigter Geschaeftsfuehrer: Frank W. Lutze
Registergericht: Amtsgericht Muenchen
Registernummer: HR 130 207
Ustnr. gemaess § 27 a Umsatzsteuergesetz: DE 206 864 106