Python variables in MySQL: INSERT INTO %s

Question

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!

you need to specify your column as well.

levi
– levi

2015-02-20 17:00:08 +00:00
Commented Feb 20, 2015 at 17:00 — levi
– levi, Commented Feb 20, 2015 at 17:00

Dominik Schmidt · Accepted Answer · 2015-02-20 17:29:16Z

2

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))

answered Feb 20, 2015 at 17:29

Dominik Schmidt

5871 gold badge9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cbeeson · Accepted Answer · 2015-02-20 17:03:39Z

0

Try:

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

answered Feb 20, 2015 at 17:03

cbeeson

763 bronze badges

Comments

cbeeson · Accepted Answer · 2015-02-20 17:22:55Z

0

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*)"""

answered Feb 20, 2015 at 17:22

cbeeson

763 bronze badges

1 Comment

user3885927 Over a year ago

just edit your previous answer instead of posting two answers.

Collectives™ on Stack Overflow

Python variables in MySQL: INSERT INTO %s

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related