Re: [Pypgsql-users] problem with invalid characters

[Timothy S. <ti...@op...>]

never mind i fixed it!

Timothy Smith wrote:

> Karsten Hilbert wrote:
>
>>> i'm wondering why i get the following error witht he code below
>>> i'm guessing it's probably somethign very simple i'm goign wrong, 
>>> most likely to do with my new line escapes \
>>> if there is a better way of formatting my query to fix this i'm all 
>>> ears.
>>>   
>>
>> consider using
>>
>> query = """
>> ... any formatting you want
>> """
>>
>> No \ needed.
>>
>> Karsten
>>  
>>
> ok i did that b ut i still get the error
>
> cur.execute("""CREATE OR REPLACE FUNCTION week_num ( date )
>  File "/usr/local/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 
> 3072, in execute
>    raise OperationalError, msg
> libpq.OperationalError: ERROR:  syntax error at or near "20040701" at 
> character 92
>
>
> code snip
>
> cur.execute("""CREATE OR REPLACE FUNCTION week_num ( date )
>                    RETURNS integer AS '
>                    SELECT ($1 - (%s::date - extract(dow from 
> %s::date)::integer + 1))/7;'
>                    LANGUAGE sql IMMUTABLE STRICT;
>                                      CREATE OR REPLACE FUNCTION eow 
> (integer)
>                    RETURNS date AS '
>                    SELECT for_date
>                    FROM transactions
>                    WHERE (transactions.for_date - (%s::date - 
> extract(dow from %s::date)::integer + 1))/7 = $1
>                       AND EXTRACT(dow FROM transactions.for_date) = 0;'
>                    LANGUAGE sql IMMUTABLE STRICT;
>                                      SELECT DISTINCT week_num(for_date),
>                       eow(week_num(for_date)),
>                       sum(dt.door_till) as door_till,
>                       sum(dt.bar) as bar,
>                       sum(dt.total) as income,
>                       sum(budget) as income_budget,
>                       sum(wages) as wages, sum(wages_budget) as 
> wages_budget,
>                       sum(reads11pm) as reads11pm,
>                       sum(door_count) as door_count,
>                       sum(dt.promo) as promo,
>                       sum(gp) as gp,
>                       (sum(dt.total) / sum(door_count))::numeric 
> (10,2) as head,
>                       sum(profit) as profit, sum(budget_profit) as 
> budget_profit,
>                       (sum(budget_profit) / sum(dt.total))::numeric 
> (10,2) as ror
>                    FROM transactions
>                       LEFT OUTER JOIN gp
>                       ON transactions.id = gp.id
>                       LEFT OUTER JOIN wages
>                       ON transactions.id = wages.id
>                       LEFT OUTER JOIN profit
>                       ON transactions.id = profit.id
>                       LEFT OUTER JOIN budgets
>                       ON transactions.id = budgets.id
>                       LEFT OUTER JOIN daily_stats
>                       ON transactions.id = daily_stats.id
>                       LEFT OUTER JOIN (SELECT t2.id, sum(CASE WHEN 
> dt.till_name LIKE 'Main Bar%%' THEN dt.cash ELSE 0 END) as bar,
>                                sum(CASE WHEN dt.till_name = 'Front 
> Door Till' THEN dt.cash ELSE 0 END) as door_till,
>                                (sum(CASE WHEN dt.till_name LIKE 'Main 
> Bar%%' THEN dt.cash ELSE 0 END) +
>                                sum(CASE WHEN dt.till_name = 'Front 
> Door Till' THEN dt.cash ELSE 0 END)) as total,
>                                sum(CASE WHEN dt.till_name = 'PR Cards' 
> OR dt.till_name = 'Other Promo' THEN dt.cash ELSE 0 END) as promo
>                            from transactions as t2, daily_takings as dt
>                            WHERE t2.id = dt.id
>                            GROUP BY t2.id
>                            ) as dt
>                        ON dt.id = transactions.id
>                       WHERE transactions.for_venue = %s
>                         AND week_num(for_date) between %s and %s
>                    GROUP BY week_num(for_date)
>                    ORDER BY week_num(for_date)"""
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> Pypgsql-users mailing list
> Pyp...@li...
> https://lists.sourceforge.net/lists/listinfo/pypgsql-users
>
>