creating Tables and columns dynamically using mysql python connector

Question

I want to create a table having columns dynamically using python list. Column Names are in the list and I want to refer them in the MySQL query. I am using MySQL.connector with python 3.4.3.

Below is the code which I tried:

col names:  ['Last Name', 'First Name', 'Job', 'Country']
table_name = 'stud_data'
query = "CREATE TABLE IF NOT EXISTS"+table_name+"("+"VARCHAR(250),".join
(col) + "Varchar(250))"
print("Query is:" ,query)
cursor.execute(query)
conn.commit()

But I am getting the following error:

mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'Name VARCHAR(250),First Name VARCHAR(250),Job VARCHAR(250),Country VARCHAR(250))' at line 1

The query looks like this:

Query is: CREATE TABLE IF NOT EXISTS stud_data (Last Name VARCHAR(250),First Name VARCHAR(250),Job VARCHAR(250),Country VARCHAR(250))

Please help on this. Thanks in advance

Eytan Avisror · Accepted Answer · 2017-12-21 13:13:59Z

2

You have spaces in the column name. i.e. 'First Name' instead of 'FirstName', removing the spaces will solve your problem. If you want to preserve the spaces, use backticks '`' to wrap the string

Sample code:

columns = [ ('Last Name', 'First Name', 'Job', 'Country') ] #list of tuples

for p in columns:
    q = """ CREATE TABLE IF NOT EXISTS stud_data (`{col1}` VARCHAR(250),`{col2}` VARCHAR(250),`{col3}` VARCHAR(250),`{col4}` VARCHAR(250)); """
    sql_command = q.format(col1=p[0], col2=p[1], col3=p[2], col4 = p[3])


>>> sql_command
' CREATE TABLE IF NOT EXISTS stud_data (`Last Name` VARCHAR(250),`First Name` VARCHAR(250),`Job` VARCHAR(250),`Country` VARCHAR(250)); '

edited Dec 21, 2017 at 13:13

answered Dec 21, 2017 at 12:25

Eytan Avisror

2,98021 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

creating Tables and columns dynamically using mysql python connector

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related