CODEWITHSUNDEEP
pythonmysqlsql
Yoseph West
Yoseph West

Reputation: 11

Creating a MySQL table's columns with a List

New to SO and fairly new to coding, so doing my best to follow the appropriate protocols.

In my python script, I'm creating a new table and populating column names from a list, named 'dups'.

dups = ['Id', 'Name', 'Price', 'Rating']

I'm inputting this list as columns for the new table, called "SuperTable", via a for loop. See code below:

with new_db:

    cur = new_db.cursor()
    cur.execute("DROP TABLE IF EXISTS SuperTable")
    for i in dups:
        if i == dups[0]:
            new_col = i.replace("'","")
            cur.execute("CREATE TABLE SuperTable(%s)" % (new_col))
    else:
        cur.execute("ALTER TABLE SuperTable ADD COLUMN %s" % i)

I've looked around a lot and can't seem to identify what I'm doing wrong. This approach worked with Sqlite but I keep getting this same error for MySQLdb:

Traceback (most recent call last):
  File "MySQL_SuperTable.py", line 125, in <module>
  cur.execute("CREATE TABLE Super(%s)" % (new_col))
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/cursors.py", line 174, in execute
  self.errorhandler(self, exc, value)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
  raise errorclass, errorvalue
  _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1")

Upvotes: 0

Views: 1510

Answers (1)

Yoseph West
Yoseph West

Reputation: 11

Thanks to eggyal! He pointed out that MySQL columns require a datatype. This is what the code looks like now (I created a list of tuples to input the datatypes + column names via a for loop):

with new_db:

cur = new_db.cursor()
cur.execute("DROP TABLE IF EXISTS SuperTable")
for i in col_namestypes:
    if i == col_namestypes[0]:
        cur.execute("CREATE TABLE SuperTable(%s %s)" % (i))
    else:
        cur.execute("ALTER TABLE SuperTable ADD COLUMN %s %s" % i)
for i in new_table:
    count = len(i)
question_marks = []
while a < count:
    question_marks.append('%s')
    a += 1
quests = ','.join(question_marks)
cur.executemany("INSERT INTO SuperTable VALUES(%s)" % quests, new_table)

Upvotes: 1

Related Questions