Creating SQL Table with variable name from Python

Question

I'm trying to create and write a DataFrame to a SQL table using Python. The table should be named after a variable, specifically table_name (table_name will change as I run my code). Below is the relevant part of my code to try to set this up:

con = sql.connect(r'/Users/linnk/Desktop/Results/Data.db')  # Creates database to write to
cur = con.cursor() 

...code...

cur.execute('''CREATE TABLE IF NOT EXISTS ''' + table_name + ''' (Date, Morning1, Day1, Evening1, Night1, Morning3, Day3, Evening3, Night3)''')

Running this gives the error (for the last line):

OperationalError: near "-": syntax error

I've tried making several modifications to the last line of my code but get similar errors. Can anyone help me spot my mistake/make the necessary adjustments to my code? For information, table_name contains a string.

Thank you in advance for any suggestions.

EDIT/UPDATE:

From what I've read it seems to be better to make a string that is then passed to cur.execute():

   stringexecute='''\'CREATE TABLE IF NOT EXISTS '''+ table_name +''' (Date, Morning1 real, Day1 real, Evening1 real, Night1 real, Morning3 real, Day3 real, Evening3 real, Night3 real)\''''
   cur.execute(stringexecute)

For information, stringexecute outputs:

stringexecute= 'CREATE TABLE IF NOT EXISTS GUTUR_400_F1-KIAGA-1 (Date, Morning1Ph real, Day1Ph real, Evening1Ph real, Night1Ph real, Morning3Ph real, Day3Ph real, Evening3Ph real, Night3Ph real)'

The code still doesn't work, however. It gives:

OperationalError: near "'CREATE TABLE ......'": syntax error

Any further help would be much appreciated. I've primarily looked at and (unsuccessfully) tried the methods from the following resources: http://www.sommarskog.se/dynamic_sql.html#objectnames https://docs.python.org/2/library/sqlite3.html

EDIT

It turns out the following works, as long as the variable table_name does not contain '-' (and possibly other symbols):

 cur.execute("CREATE TABLE IF NOT EXISTS " + table_name + " (Date, Morning1 real, Day1 real, Evening1 real, Night1 real, Morning3 real, Day3 real, Evening3 real, Night3 real)")

That appears to work - this creates 'Table1' in the database: cur.execute('''CREATE TABLE IF NOT EXISTS Table1 (Date, Morning1, Day1, Evening1, Night1, Morning3, Day3, Evening3, Night3)''') — LinnK
– LinnK, Commented Aug 10, 2015 at 5:29
Based on your last update it seems the problem was only forbidden characters. Can you regex.sub those before executing the table creation step? — James
– James, Commented Aug 10, 2015 at 15:58

Barett · Accepted Answer · 2015-08-08 16:46:26Z

3

Why don't you use:

cur.execute('CREATE TABLE IF NOT EXISTS {tab} (Date, Morning1, Day1, Evening1, Night1, Morning3, Day3, Evening3, Night3)'
   .format(tab=table_name))

edited Aug 8, 2015 at 16:46

Barett

5,9546 gold badges54 silver badges57 bronze badges

answered Aug 8, 2015 at 12:04

Ami00

1511 gold badge1 silver badge7 bronze badges

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

Comments

Ryan Gadsdon · Accepted Answer · 2017-10-20 16:10:26Z

0

Put this code:

def crear_tabla (name):
cur = conn.cursor()
cur.execute('''CREATE TABLE {tab}
  (ID INT PRIMARY KEY     NOT NULL,
  NAME           TEXT    NOT NULL,
  AGE            INT     NOT NULL,
  ADDRESS        CHAR(50),
  SALARY         REAL);'''.format(tab=name))
print ("Table created successfully")
cur.close()
conn.commit()

edited Oct 20, 2017 at 16:10

Ryan Gadsdon

2,4068 gold badges38 silver badges62 bronze badges

answered Oct 20, 2017 at 15:50

Aldo Mora

12 bronze badges

Collectives™ on Stack Overflow

Creating SQL Table with variable name from Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related