Inserting Variables MySQL Using Python, Not Working

Question

I want to insert the variable bob, and dummyVar into my table, logger. Now from what I can tell all I should need to do is, well what I have below, however this doesn't insert anything into my table at all. If I hard-code what should be written (using 'example' then it writes example to the table, so my connection and syntax for inserting is correct to this point). Any help would be more than appreciated!

conn = mysql.connector.connect(user='username', password='password!',
                              host='Host', database='database')

cursor = conn.cursor()

bob = "THIS IS AN EXAMPLE"
dummyVar = "Variable Test"

loggit = ("""
        INSERT INTO logger (logged_info, dummy)
        VALUES
            (%s, %s)
    """, (bob, dummyVar))

cursor.execute(loggit)
conn.commit()

I have also tried this:

loggit = ("""
        INSERT INTO logger (logged_info, dummy)
        VALUES
            (%(bob)s,(Hello))
    """, (bob))

and:

bob = "THIS IS AN EXAMPLE"
dummyVar = "Variable Test"

loggit = ("""
        INSERT INTO logger (logged_info, dummy)
        VALUES
            (%s, %s)
    """, (bob, dummyVar))

cursor.execute(loggit, (bob, dummyVar))
conn.commit()


cursor.execute(loggit, (bob, dummyVar))

conn.commit()

In addition to your main problem (which Martijn Pieters answers), you've got another one in your last example: `(bob)` is not a tuple of one element, it's the same thing as `bob`. You need to write `(bob,)`. — abarnert, May 12 '13 at 11:22
Does this answer your question? [MySQL parameterized queries](https://stackoverflow.com/questions/775296/mysql-parameterized-queries) — Henry Woody, May 21 '22 at 01:24

Martijn Pieters · Accepted Answer · 2013-05-12T11:37:36.133

4

You need to pass the SQL statement and the parameters as separate arguments:

cursor.execute(loggit[0], loggit[1])

or use the variable argument syntax (a splat, *):

cursor.execute(*loggit)

Your version tries to pass in a tuple containing the SQL statement and bind parameters as the only argument, where the .execute() function expects to find just the SQL statement string.

It's more usual to keep the two separate and perhaps store just the SQL statement in a variable:

loggit = """
        INSERT INTO logger (logged_info, dummy)
        VALUES
            (%s, %s)
    """
cursor.execute(loggit, (bob, dummyVar))

edited May 12 '13 at 11:37

answered May 12 '13 at 11:16

Martijn Pieters

1,048,767
296
4,058
3,343

Shouldn't I close the brackets after """ ? – Steve Byrne May 12 '13 at 11:24
@StevenByrne In the last example, no, since there's no opening one and no second value, loggit is a string, not a tuple anymore. – Joachim Isaksson May 12 '13 at 11:26
@StevenByrne: No, there is no opening parens; the `"""` tripple-quoting string can span multiple lines on it's own just fine. – Martijn Pieters May 12 '13 at 11:28
First, thank you for the help, but I'm still a bit confused, am I suppose to change only the cursor.execute(loggit) to cursor.execute(loggit, (bob, dummyVar)) Or am I suppose to also change the way I create my Insert variable? P.S. Will the same syntax work with a global variable? – Steve Byrne May 12 '13 at 11:36
@StevenByrne: You don't need to change the way you use SQL parameters, just how you call `execute()`, making sure you pass in 2 arguments. – Martijn Pieters May 12 '13 at 11:37
I updated my code to what I have under "EDIT" above, but it still does not write to the database; did I miss something? – Steve Byrne May 12 '13 at 11:41
@StevenByrne: Yes, `loggit` is *still* a tuple. It should be just a string. Compare your version of `loggit` with mine. – Martijn Pieters May 12 '13 at 11:41
Okay it works perfectly now! Thank you I can't thank you enough! – Steve Byrne May 12 '13 at 11:43
@MartijnPieters so if I have an integer say `i` how do I insert it in a table? – praxmon Apr 15 '14 at 10:57
You'd use a single-item tuple: `.execute(statement, (i,))`. – Martijn Pieters Apr 15 '14 at 11:01

Inserting Variables MySQL Using Python, Not Working

1 Answers1

Linked