extraspecialbitter extraspecialbitter - 4 months ago 15
MySQL Question

How do I create a mysql table using a variable name in Python?

I've tried to use the article Python MySQLdb execute table variable as an example, but so far no joy. I'm trying to create a table whose name is a concatenation of "archive" and the year passed in as a variable. This is an alternative to hardcoding table names, such as "archive_2013".

Here is my code snippet:

year_string = sys.argv[1]
if int(year_string) < 1999 or int(year_string) > 2014:
print "\n"
print "Year must be between 1999 and 2014\n"
sys.exit(1)

table_name = "archive_" + year_string

# Open database connection
db = MySQLdb.connect("localhost","root","menagerie","haiku_archive" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Create table using execute() method.

sql = ""CREATE TABLE IF NOT EXISTS %s" % table_name
haiku_text VARCHAR(120),
date_written CHAR(22))"
cursor.execute(sql)


Here is the error I'm getting:

pablo@desktop=> ./insert_haiku_from_file_into_table.py 2013 qwert.txt
File "./insert_haiku_from_file_into_table.py", line 36
sql = ""CREATE TABLE IF NOT EXISTS %s" % table_name
^
SyntaxError: invalid syntax


Any help would be greatly appreciated!




I tried implementing the responses I've received, but so far with unsatisfactory results. Here is my snippet using the triple-quoted SQL:

sql = """CREATE TABLE IF NOT EXISTS %
haiku_text VARCHAR(120),
date_written CHAR(22))""" % table_name
cursor.execute(sql)


I end up getting the following when I execute the script:

pablo@desktop=> ./insert_haiku_from_file_into_table.py 2013 qwert.txt
Traceback (most recent call last):
File "./insert_haiku_from_file_into_table.py", line 38, in <module>
date_written CHAR(22))""" % table_name
ValueError: unsupported format character '
' (0xa) at index 28


I also tried using place holder notation, as I'd like to avoid even the remotest possibility of SQL injection. Here's my snippet:

sql = """CREATE TABLE IF NOT EXISTS ?
haiku_text VARCHAR(120),
date_written CHAR(22))"""
cursor.execute(sql, table_name)


Here is what happens when I execute:

pablo@desktop=> ./insert_haiku_from_file_into_table.py 2013 qwert.txt
Traceback (most recent call last):
File "./insert_haiku_from_file_into_table.py", line 39, in <module>
cursor.execute(sql, table_name)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute
query = query % db.literal(args)
TypeError: not all arguments converted during string formatting


I'll do some further research on the syntax of the place holder, but in the mean time any further suggestions would be great!

Answer

Go old school

sql = "CREATE TABLE IF NOT EXISTS " + table_name  + """
         haiku_text VARCHAR(120), 
         date_written CHAR(22))"""

print sql# check if printed correctly