CODEWITHSUNDEEP
pythonsqlpandasdataframe
LinnK
LinnK

Reputation: 395

Creating SQL Table with variable name from Python

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)")

Upvotes: 1

Views: 4969

Answers (2)

Aldo Mora
Aldo Mora

Reputation: 1

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()

Upvotes: 0

Ami00
Ami00

Reputation: 151

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))

Upvotes: 3

Related Questions