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, May 02 '17 at 17:02
You print out the tableName variable. Does it show the correct table name? — Shadow, May 02 '17 at 17:07
@Shadow : Thanks for editing my post. Yes it shows the correct name of the first table! — Zryan, May 02 '17 at 17:11
In this case why do you use `tableName[m]` form when you create the sql statement? Use simply `tableName`. — Shadow, May 02 '17 at 17:14
@TimothyG. the database contains many tables (more than 56,000 tables) — Zryan, May 02 '17 at 17:15
It's in a loop though. `tablename` should change with each iteration. — Timothy G., May 02 '17 at 17:17
@Shadow I forget to take out [m] when I posted. I can't edit my post now, could you please take them out of the code. Thanks — Zryan, May 02 '17 at 17:17
56k tables??? I hope that was a joke or you mixed up number of records with number of tables — Shadow, May 02 '17 at 17:18
Are you saying that your code does not contain `tableName[m]`? — Shadow, May 02 '17 at 17:21

score 1 · Accepted Answer · answered May 02 '17 at 17:16

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 Answers1

Linked