Error: sqlite3.OperationalError: no such table: main.m

Question

I have a very simple code in python, basically I try to create indexes for each table in my database (already exists). The database and my script file are located at the same folder. After fetching a list of names of the tables and trying to create indexes for the first table, I get this error message:

c.execute("CREATE INDEX IF NOT EXISTS "+tableName[m]+"Date ON "+tableName[m]+" (date)"); sqlite3.OperationalError: no such table: main.m

The database doesn't have any table with this name (main.m)!

my code:

import sqlite3
DBname = 'myDatabase.sqlite';
# connect to the database
conn = sqlite3.connect(DBname);
c    = conn.cursor();
c.execute("SELECT name FROM sqlite_master WHERE type='table'");
tables = c.fetchall();
print("\nCreating the indices for each table in the database ...");
for m in range (0,len(tables)):
    tableName = tables[m][0];
    print(tableName)
    c.execute("CREATE INDEX IF NOT EXISTS "+tableName[m]+"Date ON "+tableName[m]+" (date)");
    c.execute("CREATE INDEX IF NOT EXISTS "+tableName[m]+"Year ON "+tableName[m]+" (year)");
conn.close()

Thanks for your help!

MySQL has nothing to do with SQLite, these are different products. — Shadow
You print out the tableName variable. Does it show the correct table name? — Shadow
@Shadow : Thanks for editing my post. Yes it shows the correct name of the first table! — Zryan
In this case why do you use tableName[m] form when you create the sql statement? Use simply tableName. — Shadow

Shadow Shadow · Accepted Answer · 2017-05-02T17:16:34

If the tableName variable shows the correct table names as per your comment, then just use tableName when you create the sql statement instead of tableName[m]:

c.execute("CREATE INDEX IF NOT EXISTS "+tableName+"Date ON "+tableName+" (date)");
c.execute("CREATE INDEX IF NOT EXISTS "+tableName+"Year ON "+tableName+" (year)");

Error: sqlite3.OperationalError: no such table: main.m

1 Answers