CODEWITHSUNDEEP
pythonmysql
andreas
andreas

Reputation: 21

Python variables in MySQL: INSERT INTO %s

This question has been asked bevor (here), but no answer is working for me and unfortunally I am not allowed to add a comment, because I'm new here. I didn't know what else to do than asking the question again, sorry for this - please tell me the right way to do.

I want to insert Python variables into a MySQL table named by a Python variable. I figured out, to create the table by:

curs.execute ("""CREATE TABLE IF NOT EXISTS %s LIKE table""" %(today))

I also figured out to insert values like this:

curs.execute (
        """ INSERT INTO table (column)
            VALUES (%s) """,
            (variable))

Now I tried

today = "table_name"
variable = "name"
curs.execute (
        """ INSERT INTO %s (column)
            VALUES (%s) """,
            ( table, variable ))

I'll get this error:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table_name' (column …' at line 1")

I also tried:

today = "table_name"
variable = "name"
curs.execute (
            """ INSERT INTO %s (column)
                VALUES (%s) """
                % ( table, variable ))

I'll get this error:

(1054, "Unknown column 'name' in 'field list'")

I guess there's something wrong with the strings …

Thank you for answers!

Upvotes: 2

Views: 6313

Answers (3)

Dominik Schmidt
Dominik Schmidt

Reputation: 557

Try replacing the %s with ? and let sqlite handle the insertion. (Also helps preventing SQL injection attacks in web applications)

table_name = "today"
variable = "name"
curs.execute ("INSERT INTO ? (column) VALUES (?)",(table_name, variable))

Upvotes: 2

cbeeson
cbeeson

Reputation: 76

You have muddled your variables in your third, not working code snippet. instead try:

table_name = "today"
variable = "name"
curs.execute (
    """ INSERT INTO %s (column)
        VALUES (%s) """,
        ( table_name, variable ))

This will create a table named 'Today' with one column named 'column' with one data value in it 'name'. For this to work you will need to have previously created a table that has this column available. So your create code needs to change:

"""create table "today" ("column" varchar2(16BYTE), *add extra columns here if you wish*)"""

Upvotes: 0

cbeeson
cbeeson

Reputation: 76

Try:

today = "table_name"
variable = "name"
curs.execute (
        """ INSERT INTO :table_name (column)
            VALUES (:variable_name) """,
            {'table_name': today, 'variable_name': variable})

Upvotes: 0

Related Questions