inserting into tables created on the fly

[bugman_2000]

Hello.

I've been looking all over for an example of what I'm trying to do, but I haven't had any luck finding it. Basically, I want to insert values into a table which has been created on the fly and whose name is in a variable. I've tried a variety of ways to do this. I managed to create the table simply by concatenating the MySQL command string, but because I have some values that need escaping, I want to use the built-in escaping of the %s placeholders.

However, with the triple quoted string, I don't know how to get the value of the table name into the query. A command along the lines of the one below does not seem to work. Can somebody tell me the right way to do this?

cursor.execute("""INSERT INTO %s VALUES (%s, %s , %s)""",(tablename, value1, value2, value3))

Thanks very much,

Tony

[Andy Dustman]

You cannot insert table or column names by the execute() method; only parameter values can be inserted. This is a common feature/limitation of all DB-API interfaces.

Change:

cursor.execute("""INSERT INTO %s VALUES (%s, %s , %s)""",(tablename, value1, value2, value3))

to:

values = value1, value2, value3
query = """INSERT INTO %s VALUES (%s)""" % (tablename, ",".join(["%s"]*len(values))
cursor.execute(query, values)

or better still:

def placeholders(values):
return ",".join(["%s"]*len(values)

def columnnames(columns):
return ",".join(["%s" % c for f in columns])

query = """INSERT INTO %s (%s) VALUES (%s)""" % (tablename, columnnames(columns), placeholders(values))
cursor.execute(query, values)

Better still if you use dictionaries; left as an exercise for the reader.

[Lisca75]

I'm sorry for the stupid question...
Marco

[Lisca75]

Hi Andy,
I've the same problem of Tony.
I've changed my query format follow your suggestions, but when I run the script I obtain a syntax error.

this is my code:

import MySQLdb

db=MySQLdb.connect(user="pippo", passwd="pluto", db="pippo")
c=db.cursor()
value= post.date, post.link, post.title, post.source, post.summary, post.description, post.language
q="""INSERT INTO %s (%s) VALUES (%s)"""%(sys.argv[1],",".join(["%s"]*len(value))
c.execute(q, value)

and the error is:

File "parser.py", line 97
c.execute(q, value)
^
SyntaxError: invalid syntax

can yu help me?

thank you very much
Marco

[Andy Dustman]

I think you left off a closing parenthesis on the previous line.