Re: [Pypgsql-users] problem with invalid characters

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

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)"""