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-30T04:21:28.423

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 '19 at 04:21

answered Feb 11 '15 at 23:55

Rafa Moyano

169
2
8

3

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. – Conspicuous Compiler Feb 12 '15 at 00:19
1

But it's using mysql.connector which is DB API v2.0 compliant. Why it should scape the string manually? – Rafa Moyano Feb 12 '15 at 00:27
See http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement – Conspicuous Compiler Feb 12 '15 at 16:44
@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. – Rafa Moyano Feb 12 '15 at 22:44
escape_string is included and documented here - https://dev.mysql.com/doc/connector-python/en/connector-python-api-cext-escape-string.html – evan_b May 28 '17 at 00:28

Replace table name with variable. Using python and mysql connector

1 Answers1

Linked

Related