MySQL syntax error using python to add column to a table

Question

The code i have is:

for key in keys:
    cursor.execute("""
                    ALTER TABLE segment_table ADD %s VARCHAR(40)
                    """, key)

I get a error telling me my syntax is wrong. When I replace the %s with a actual string the syntax error goes away.

for key in keys:
    cursor.execute("""
                    ALTER TABLE segment_table ADD myColumn VARCHAR(40)
                    """)

Any help is appreciated.

Answer 1

Shouldn't you do the replacement before feeding it?

query = "ALTER TABLE segment_table ADD %s VARCHAR(40)" % (key)
cursor.execute( query )

Answer 2

when cursor.execute() replace %s in a query string it adds ' ' to the argument values supplied...so when you do

key = 'abc'
cursor.execute("""
                ALTER TABLE segment_table ADD %s VARCHAR(40)
                """, key)

the query executed is

ALTER TABLE segment_table ADD 'abc' VARCHAR(40)

to which mysql will throw a syntax error coz the column names, table names can be in `` but not ' '

so this will work

query = "ALTER TABLE segment_table ADD %s VARCHAR(40)" % (key)

Answer 3

There is a bit of confusion going here, for several reasons:

(1) mySQL uses the % as a parameter marker -- easily confused with the % in Python's string % (data1, data2, etc)

(2) some people seem not to be aware that parameter markers can be used only where an expression can be used in SQL syntax -- this excludes table names, column names, function names, keywords, etc

(3) code-golf onelinerism

Required SQL: ALTER TABLE segment_table ADD myColumn VARCHAR(40)

Using a parameter doesn't work:

key = "myColumn"
sql = "ALTER TABLE segment_table ADD %s VARCHAR(40)" # col name not OK as parm
cursor.execute(sql, (key, ))

You need to build an acceptable SQL statement, using e.g. Python string formatting:

key = "myColumn"
sql = "ALTER TABLE segment_table ADD %s VARCHAR(40)" % key
cursor.execute(sql)