Django + south + python: strange behavior when using a text string received as a parameter in a function
This is my first question.
I am trying to execute a SQL query in django (south migration):
from django.db import connection
# ...
class Migration(SchemaMigration):
# ...
def transform_id_to_pk(self, table):
try:
db.delete_primary_key(table)
except:
pass
finally:
cursor = connection.cursor()
# This does not work
cursor.execute('SELECT MAX("id") FROM "%s"', [table])
# I don't know if this works.
try:
minvalue = cursor.fetchone()[0]
except:
minvalue = 1
seq_name = table + '_id_seq'
db.execute('CREATE SEQUENCE "%s" START WITH %s OWNED BY "%s"."id"', [seq_name, minvalue, table])
db.execute('ALTER TABLE "%s" ALTER COLUMN id SET DEFAULT nextval("%s")', [table, seq_name + '::regclass'])
db.create_primary_key(table, ['id'])
# ...
I use this function like this:
self.transform_id_to_pk('my_table_name')
Therefore, he must:
- Find the largest existing id or 0 (it will fail)
- Create sequence name
- Create sequence
- Update id field to use sequence
- Update ID as PC
But it crashes and the error says:
File "../apps/accounting/migrations/0003_setup_tables.py", line 45, in forwards
self.delegation_table_setup(orm)
File "../apps/accounting/migrations/0003_setup_tables.py", line 478, in delegation_table_setup
self.transform_id_to_pk('accounting_delegation')
File "../apps/accounting/migrations/0003_setup_tables.py", line 20, in transform_id_to_pk
cursor.execute(u'SELECT MAX("id") FROM "%s"', [table.encode('utf-8')])
File "/Library/Python/2.6/site-packages/django/db/backends/util.py", line 19, in execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: relation "E'accounting_delegation'" does not exist
LINE 1: SELECT MAX("id") FROM "E'accounting_delegation'"
^
I have shortened the file paths for convenience.
What does this "E'accounting_delegation" mean? How can I get rid of it?
Thanks!
Carlos.
a source to share
The problem is that you are using DB-API parameterization for things that are not SQL data. When you do something like:
cursor.execute('INSERT INTO table_foo VALUES (%s, %s)', (col1, col2))
the DB-API module (django's interface to whatever database you use in this case) will know to escape the contents of "col1" and "col2" respectively and replace% s with them. Note that there are no quotes in percent% s. But this only works for SQL data, not for SQL metadata such as table names and sequence names, because they need to be specified differently (or not at all). When you do
cursor.execute('INSERT INTO "%s" VALUES (%s, %s)', (tablename, col1, col2))
tablename is quoted as if you were assuming it was the string data to be inserted, and you end up with, for example, "table_foo". You need to separate your SQL metadata, which is part of the query, and your SQL data, which is not the case:
sql = 'INSERT INTO TABLE "%s" VALUES (%%s, %%s)' % (tablename,)
cursor.execute(sql, (col1, col2))
Note that since the django DB-API frontend paramstyle is "pyformat" (it uses% s for placeholders), you need to avoid them when formatting strings to create the SQL you want to execute. Note that this does not protect against SQL injection attacks where you grab the table name from an unsafe source and do not validate it.
a source to share