6

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!

Community
  • 1
  • 1
extraspecialbitter
  • 67
  • 1
  • 4
  • Do **not** use string formatting to parametrize an SQL query. Otherwise you'll insert a serious security hole: [SQL injection](http://en.wikipedia.org/wiki/SQL_injection). You can put a place holder and pass the parameter to the `execute` method of the cursor: `sql = """CREATE TABLE IF NOT EXISTS ? etc. """; cursor.execute(sql, table_name)`. You can check the syntax for the placeholder in the global: `MySQLdb.paramstyle`. See the [spec](http://legacy.python.org/dev/peps/pep-0249/#paramstyle) for more details. – Bakuriu Apr 30 '14 at 17:56
  • I hadn't considered the dilemma of SQL injection because the variable is passed in by another python script, but I suppose it's best practice to avoid it entirely. Thanks for the head's up! – extraspecialbitter Apr 30 '14 at 18:40
  • @Bakuriu Parameters can't be used for table names, only in places where expressions are allowed. – Barmar Apr 30 '14 at 20:14
  • I had heard someplace else that parameters couldn't be used for table names. I was hoping that it wasn't the case. – extraspecialbitter Apr 30 '14 at 22:21

6 Answers6

5

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
4

Try:

sql = """CREATE TABLE IF NOT EXISTS %s
         haiku_text VARCHAR(120), 
         date_written CHAR(22))""" % table_name
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I tried the above syntax and received the following error: `pablo@pmenanx1=> ./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 date_written CHAR(22))""" % table_name ValueError: unsupported format character ' ' (0xa) at index 28` – extraspecialbitter Apr 30 '14 at 18:45
  • 1
    I only get that error if I leave out the `s` in `%s`. – Barmar Apr 30 '14 at 20:13
4

For those who use %s both in table name and insert values, that doesn't work, you need use different formate method like this:

sql = "insert into {table} (f1,f2,f3) values (%s, %s, %s)"
cursor.execute(sql.formate(table="student"), ("name", "age", "score"))

BELOW IS THE WRONG ONE:

sql = "insert into %s (f1,f2,f3) values (%s, %s, %s)"
cursor.execute(sql("student", "name", "age", "score"))
shellbye
  • 4,620
  • 4
  • 32
  • 44
0

You can try the following code:

sql = """CREATE TABLE IF NOT EXISTS `%s`
         haiku_text VARCHAR(120), 
         date_written CHAR(22))""" % (table_name)
techydesigner
  • 1,681
  • 2
  • 19
  • 28
Lugia5133
  • 1
0

table_name = 'users' sql = "CREATE TABLE IF NOT EXISTS " + table_name + "(name VARCHAR(90), age INTEGER(3))"

Raj kumar
  • 3
  • 2
  • Kindly try to add more details to you answer. Tell everyone how it solves the problem being faced by the questioner. Also try to format your answer so its easily readable and everyone knows which part of it deals with code and which part is an explanation. – Hussain Nawaz Lalee Apr 15 '20 at 14:34
-1

Just another perspective, not really solving above problem but table names will be changed.

my_list = [*range(1999,2021, 1)]

for year in my_list:
    
    sql = "CREATE TABLE archive_" + str(year)+" (category varchar(255), jan" + str(year)+ " varchar(255), feb" + str(year)+ " varchar(255) )"""
    print(sql) 

CREATE TABLE archive_1999 (category varchar(255), jan1999 varchar(255), feb1999 varchar(255) )
CREATE TABLE archive_2000 (category varchar(255), jan2000 varchar(255), feb2000 varchar(255) )
CREATE TABLE archive_2001 (category varchar(255), jan2001 varchar(255), feb2001 varchar(255) )
CREATE TABLE archive_2002 (category varchar(255), jan2002 varchar(255), feb2002 varchar(255) )
CREATE TABLE archive_2003 (category varchar(255), jan2003 varchar(255), feb2003 varchar(255) )
CREATE TABLE archive_2004 (category varchar(255), jan2004 varchar(255), feb2004 varchar(255) )
CREATE TABLE archive_2005 (category varchar(255), jan2005 varchar(255), feb2005 varchar(255) )
CREATE TABLE archive_2006 (category varchar(255), jan2006 varchar(255), feb2006 varchar(255) )
CREATE TABLE archive_2007 (category varchar(255), jan2007 varchar(255), feb2007 varchar(255) )
CREATE TABLE archive_2008 (category varchar(255), jan2008 varchar(255), feb2008 varchar(255) )
CREATE TABLE archive_2009 (category varchar(255), jan2009 varchar(255), feb2009 varchar(255) )
CREATE TABLE archive_2010 (category varchar(255), jan2010 varchar(255), feb2010 varchar(255) )
CREATE TABLE archive_2011 (category varchar(255), jan2011 varchar(255), feb2011 varchar(255) )
CREATE TABLE archive_2012 (category varchar(255), jan2012 varchar(255), feb2012 varchar(255) )
CREATE TABLE archive_2013 (category varchar(255), jan2013 varchar(255), feb2013 varchar(255) )
CREATE TABLE archive_2014 (category varchar(255), jan2014 varchar(255), feb2014 varchar(255) )
CREATE TABLE archive_2015 (category varchar(255), jan2015 varchar(255), feb2015 varchar(255) )
CREATE TABLE archive_2016 (category varchar(255), jan2016 varchar(255), feb2016 varchar(255) )
CREATE TABLE archive_2017 (category varchar(255), jan2017 varchar(255), feb2017 varchar(255) )
CREATE TABLE archive_2018 (category varchar(255), jan2018 varchar(255), feb2018 varchar(255) )
CREATE TABLE archive_2019 (category varchar(255), jan2019 varchar(255), feb2019 varchar(255) )
CREATE TABLE archive_2020 (category varchar(255), jan2020 varchar(255), feb2020 varchar(255) )
Shashi Bhushan
  • 1