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.

Answer 1

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