Replace table name with variable. Using python and mysql connector

Question

I would like to dynamically change the variable name of the table I insert data into.

This currently works,

def dataEntry(subreddit, _title, _post_url, _imageURL):
    cnx = mysql.connector.connect(**config)

    c = cnx.cursor()
    insert = ("""INSERT INTO FoodPorn
                    (subreddit, title, post_url, imageURL)
                    VALUES (%s, %s, %s, %s)""")

    data_value = (subreddit, _title, _post_url, _imageURL)

    c.execute(insert, data_value)
    cnx.commit()
    c.close()
    cnx.close()

dataEntry("fake", "fake", "fake", "fake")

but when I try and do the same for the table name in this case "FoodPorn", but for a dynamic one such as in this example MachinePorn,

def dataEntry(subreddit, _title, _post_url, _imageURL):
    cnx = mysql.connector.connect(**config)

    c = cnx.cursor()
    insert = ("""INSERT INTO subredditName
                    (subreddit, title, post_url, imageURL)
                    VALUES (%s, %s, %s, %s, %s)""")

    data_value = ("MachinePorn", subreddit, _title, _post_url, _imageURL)

    c.execute(insert, data_value)
    cnx.commit()
    c.close()
    cnx.close()

dataEntry("fake", "fake", "fake", "fake")

I get this error,

mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'sytykr.subredditname' doesn't exist

This leads me to believe I cannot do it this way and so I would like to ask How can I do it so I can eventually pass a variable name in of the Table, instead of having to hard code it each time.

Rafa Moyano · Accepted Answer · 2019-04-30 04:21:28Z

9

The exception that is showing mysql connector is telling you that the table doesn't exist in your database.

In addition, you're trying to use 'MachinePorn' as the argument but you didn't define that in the query, it's hardcoded 'subredditName'.

I think you should define database as another parameter in the query and it will run fine:

def dataEntry(subreddit, _title, _post_url, _imageURL):
     cnx = mysql.connector.connect(**config)

     c = cnx.cursor()
     insert = cnx.escape_string("INSERT INTO MachinePorn (subreddit, title, post_url, imageURL) VALUES (%s, %s, %s, %s)")

     data_value = (subreddit, _title, _post_url, _imageURL)

     c.execute(insert, data_value)
     cnx.commit()
     c.close()
     cnx.close()

dataEntry("fake", "fake", "fake", "fake")

edited Apr 30, 2019 at 4:21

answered Feb 11, 2015 at 23:55

Rafa Moyano

1813 silver badges11 bronze badges

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

5 Comments

Conspicuous Compiler Over a year ago

This is partially correct. You can't parameterize a table name in a prepared statement. You need to manually concatenate the query string with the (preferably escaped via cnx.escape_string()) table name.

Rafa Moyano Over a year ago

But it's using mysql.connector which is DB API v2.0 compliant. Why it should scape the string manually?

Conspicuous Compiler Over a year ago

See stackoverflow.com/questions/11312737/…

Rafa Moyano Over a year ago

@ConspicuousCompiler I've edited the code as you suggested. The escape_string method is not present in mysql connector library, if you use MySQLdb you'll be able to call it.

evan_b Over a year ago

escape_string is included and documented here - dev.mysql.com/doc/connector-python/en/…

Collectives™ on Stack Overflow

Replace table name with variable. Using python and mysql connector

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related