sqlite3 OperationalError: no such table: table

[dusk2dawm]

import sqlite3

datafile = 'XZ704.DB'
datadir = '/full_path_to/SQLITE_FILES/'
db = datadir+datafile

conn = sqlite3.connect(db)
cur = conn.cursor()

cur.execute("SELECT * FROM Airports LIMIT 1")

OperationalError: no such table: Airports

That works fine in a terminal but for some reason python 2.7 no like.

I have tried full path, relative path and same directory. No joy.

Google shows lot's of same problem but none of the suggested answers works on my linux (Ubuntu 15.04 MATE Desktop Environment 1.8.2) installation.

A previous poster here saw the light refering to "read the "Multidb and Database Router" section. Router is the answer" but I can't find it here (possibly some Django man pages?)

Anyone, plize?

[rproffitt]

This looks like one of those tutorials. But Airports was always airports.
http://www.sqlines.com/postgresql/oid

Check your capitalization.

[cereal]

Hi!

In addition to previous suggestion: if the path is wrong or does not have write permissions Python would return:

sqlite3.OperationalError: unable to open database file

Instead you get:

sqlite3.OperationalError: no such table: Airports

Which can be generated if:

1. the database file name is wrong due, for example, to the case: linux is case sensitive, Mac OS no (at least not by default)
2. the database file or the parent directory is read-only, so you have to change the permissions
3. the table does not exists

In the first case connect() will create the database file, but this obviously won't have the Airports table.

In the first case this:

for row in cur.execute('''SELECT "Hello"'''):
    print row

will run successfully, it will run successfully also if the file is read-only, but it will fail if there are permission issues with the parent directory. The error, however, will be related to the database file, not to the table.

[dusk2dawm]

rproffitt, well - capitalization may be important in postgress but not in sqlite. In this case our vendor spelled table "Airports" as "Airports" but in a terminal "aIrPoRtS" will do just fine if one is so inclined.

cereal, the path is correct. To be sure I copied it from a file manager. The permissions of directory and file are set correctly as well. The table exists - I've been working with it in a terminal while trying to make this python script.

Meanwhile I did some editing and the new version works fine:

#!/usr/bin/python
import sqlite3

datafile = 'XZ704.DB'
datadir = '/full_path_to/SQLITE_FILES/'
datadir = '../SQLITE_FILES/'
db = datadir+datafile

conn = sqlite3.connect(db)
conn.text_factory = sqlite3.OptimizedUnicode
cur = conn.cursor()

for row in cur.execute('SELECT Ident FROM Airports'):
    print row

conn.close()

Why overwriting the the first content of "datadir" made a difference...???
conn.text_factory = sqlite3.OptimizedUnicode was inserted after the script started working.

For now I consider my problem solved - but I'm still puzzled.

Thanks!

[rproffitt]

Why overwriting the the first content of "datadir" made a difference...?

Sounds like file permissions or rights issues to me. Your app runs as some user and the overwrite creates the file with the rights of the app and its context.

I find this most often happens under Windows. Linux, not so much. What was the host OS?