Create SQLite Table With Variable

March 27, 2022

I want to create three tables fremdgehen.com, molligundwillig.de and reifer6.de.

But I am not able to transfer each element to CREATE TABLE. Why?

import sqlite3

con = sqlite3.connect('emails.db')
cur = con.cursor()

pages = ['fremdgehen.com', 'molligundwillig.de', 'reifer6.de']

for i in pages:
    try:
        sql_command = f'''
        CREATE TABLE {i} (
            email INTEGER,
            password VARCHAR,
            PRIMARY KEY (id));
        cur.executescript(sql_command)
        con.commit()
        '''
    except:
        pass

con.close()

>Solution :

Since the table names contain a . character, you have to escape the names.

        sql_command = f'''
        CREATE TABLE `{i}` (
            email INTEGER,
            password VARCHAR,
            PRIMARY KEY (id));
        cur.executescript(sql_command)
        con.commit()
        '''

Note that having . in table and column names is inconvenient, because . is the separator between database, table, and column names. So you’ll have to escape the table name in all your queries. Consider removing the . before using it as a table name to simplify this.