Thread: [Pypgsql-users] problem with invalid characters

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

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.
thanks in advance

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

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)", (YearDate, YearDate, 
YearDate, YearDate,Venue, FromWeek, ToWeek))

Thread: [Pypgsql-users] problem with invalid characters

pypgsql-users