Creating a sqlite table with Python3

Question

I am using the sqlite3 package to create, connect to, and manage a sqlite database via a class implementation. In one method I have created a database, and in the next method I am trying to create a table within that database using the parameter substitution with ?. However, sqlite will not accept the command. Where am I going wrong and how do I get python-sqlite to accept the execute command to create the table headers? My sample code is attached below.

class ManageDatabase:
 def __init__(self, database, table_name):
 self.database = database
 self.table_name = table_name
 def create_database(self):
 # This function works so I will omit the details
 def connect_to_database(self):
 # This function also works
 self.conn = sqlite3.connect(self.database)
 self.cur = self.conn.cursor()
 def create_table(self, headers):
 # headers = ['id integer primary key', 'Column1 REAL', 'Column2 TEXT']
 # - In this case, headers has 3 entries, but this can be whatever 
 # the user wants and the code must be able to adapt
 ''' This is where the code runs into trouble '''
 query = 'CREATE TABLE {tn} (?);'.format(tn=self.table_name)
 self.cur.execute(query, (*headers, ))
 self.conn.commit()
if __name__ == "__main__":
 data = ManageDatabase('Test.db', 'db_table')
 # - The database already exists, but does not have a table,
 # so there is no need to use data.create_database()
 data.connect_to_database() # This command executes successfully
 headers = ['id integer primary key', 'Column1 REAL', 'Column2 TEXT'] 
 data.create_table(headers)

At this point I get the error sqlite3.OperationalError: near "?": syntax error. How can I write this code such that it works with the data in the headers list and that can also be flexible enough to allow a user to enter in 5, 6, or however many header columns and datatypes they choose?

• michael_heath: The issue is that (?) is one value (sql escaped). You are trying to insert 3 items so you would need (?,?,?). Table names and column names are not suitable for the sql escaped value concept as it gets single quoted and internal single quotes are doubled up etc. Your 1st header would become 'id integer primary key' which would be incorrect. Although I have not tested it with table or column names.

Answer 1

A possible solution is to concatenate the data with format and with the help of join:

def create_table(self, headers):
 try:
 query = 'CREATE TABLE {tn} ({fields})'.format(tn=self.table_name, fields=",".join(headers))
 self.cur.execute(query)
 self.conn.commit()
 except sqlite3.Error as er:
 print(er)