How to create multiple sql tables in a loop with python

Question

So I am writing this program that converts the .csv files I have and in return exports the converted files to the database. The .csv files all have the same columns and I am trying to create multiple tables using a loop and i get this error.

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '''(\n
{} int,\n ' at line 1")

CODE:

country_index = input('Country Code: ')

def database_uploader():

  conn = pymysql.connect(host='localhost',
                         user='test_user',
                         password='',
                         db='%s'%country_index)
  cur = conn.cursor()

  path = r'C:\Users\Robin\Desktop\usa_indicator'
  filenames = glob.glob(path + '/*.csv')
  dfs = []
  for files in filenames:
        f = open(files)
        fString = f.read()
        fName = files[37:2]

        for lines in fString.split('\n'):

              dfs.append(lines.split(','))

              DATE = dfs[0][1]; REALTIME_START = dfs[0][2]; VALUE = dfs[0][3]



              queryCreateTable = """CREATE TABLE '%s'(
                                    {} int,
                                    {} int,
                                    {} int
                                    )"""%fName.format(DATE, REALTIME_START, VALUE)

              cur.execute(queryCreateTable)

  conn.close()

The table name should not be in single quotes. See https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Barmar, Sep 18 '18 at 20:58
`DATE = dfs[0][1]; REALTIME_START = dfs[0][2]; VALUE = dfs[0][3]` will use the same values each time through the loop. Why aren't you using the current value of `lines` for that iteration? — Barmar, Sep 18 '18 at 21:05
I am looping through the names that I want my tables to be. so I am using the %s with my variable to assign them those values. I am certain that it has to do with the .format but I have no idea how else would I assign the table names. — Robex, Sep 18 '18 at 21:08
The DATE etc are the column names. I do not know how I would assign column names other than the .format() method — Robex, Sep 18 '18 at 21:11
You're creating the same table `fName` each time through the inner loop. — Barmar, Sep 18 '18 at 21:12
`dfs.append` adds to the end of the list, so `dfs[0]` doesn't change each time through the loop. — Barmar, Sep 18 '18 at 21:13
You probably want `dfs[-1][0]`, `dfs[-1][1]`, and `dfs[-1][2]` to get the elements that you just appended to the list. — Barmar, Sep 18 '18 at 21:14

score 0 · Answer 1 · answered Sep 18 '18 at 21:11

Operator precedence is causing your mixture of % and .format() to work differently than you expect. . has higher precedence than %, so it's being executed as if you'd written.

         queryCreateTable = """CREATE TABLE '%s'(
                                {} int,
                                {} int,
                                {} int
                                )"""%(fName.format(DATE, REALTIME_START, VALUE))

You need to add parentheses to override this parse:

         queryCreateTable = ("""CREATE TABLE '%s'(
                                {} int,
                                {} int,
                                {} int
                                )"""%fName).format(DATE, REALTIME_START, VALUE)

or you could just use a single formatting operator:

         queryCreateTable = """CREATE TABLE `{}`(
                                `{}` int,
                                `{}` int,
                                `{}` int
                                )""".format(fName, DATE, REALTIME_START, VALUE)

Also, table and column names should be in backticks, not single quotes.

How to create multiple sql tables in a loop with python

1 Answers1