[SQLite] "no such table" error

[tatarinova]

Hello,

I got this error:

 {skipped}
 db_cursor.execute("select * from SITES where include_in_maxref_comp=?",'0')
 pyodbc.Error: ('HY000', '[HY000] [SQLite]no such table: SITES (1) (1) (SQLPrepare)')

while I run this simple script:

import pyodbc
dsn = 'TestDB'
database = 'OPER_odc.db' # the DB is located in the same place as the script; also tried with the absolute path
connection_string = "DRIVER={SQLITE3}; SERVER=localhost; DATABASE=database; Trusted_connection=yes; DNS=dns"
db_connection  = pyodbc.connect(connection_string)
db_cursor = db_connection.cursor()
db_cursor.execute("select * from SITES where include_in_maxref_comp=?",'0')

My $HOME/.odbc.ini is the following:

[TestDB] Description=My test sqlite database Driver=SQLite3 Database=/absolute/path/OPER_odc.db

The table 'SITES' exists:

$ sqlite3 OPER_odc.db SQLite version 3.12.2 2016-04-18 17:30:31 Enter ".help" for usage hints. sqlite> .tables COMPOSITES SCANS VOLUMES
ICAO_CODES SITES
MET_CENTRES TRANSMISSION_METHODS

Also, when I connect to the DB using sqlite3 library, it works ok.

Environment

• Python: 2.7.12
• pyodbc: 4.0.1 (also tried with 3.1.1)
• OS: Linux 4.8.2-server-1.mga6 x86_64
• DB: SQLite
• driver: SQLite ODBC 0.9995 from here
• unixODBC: 2.3.4

[gordthompson]

Your statement

connection_string = "DRIVER={SQLITE3}; SERVER=localhost; DATABASE=database; Trusted_connection=yes; DNS=dns"

produces a connection string that is malformed, so the SQLite ODBC driver treats the whole thing as the database name. When I run your code and then look in my home directory I see

gord@xubuntu64-nbk1:~$ ls -l
total 36
drwxr-xr-x 2 gord gord 4096 Jul 21 18:19 Desktop
drwxr-xr-x 2 gord gord 4096 Jul 21 18:19 Documents
drwxr-xr-x 3 gord gord 4096 Aug 10 06:59 Downloads
-rw-r--r-- 1 gord gord    0 Aug 10 07:17 DRIVER={SQLITE3}; SERVER=localhost; DATABASE=database; Trusted_connection=yes; DNS=dns
drwxr-xr-x 2 gord gord 4096 Jul 21 18:19 Music
drwxr-xr-x 2 gord gord 4096 Jul 21 18:19 Pictures
drwxr-xr-x 2 gord gord 4096 Jul 21 18:19 Public
-rw-rw-r-- 1 gord gord  434 Aug 10 07:17 sqlite_test.py
drwxr-xr-x 2 gord gord 4096 Jul 21 18:19 Templates
drwxr-xr-x 2 gord gord 4096 Jul 21 18:19 Videos

Notice the file named "DRIVER={SQLITE3}; SERVER=localhost; DATABASE=database; Trusted_connection=yes; DNS=dns".

Not only do you include the database variable as a literal, but you also include connection parameters that have no meaning to the SQLite ODBC driver, e.g., Trusted_connection.

Your connection string should look more like this:

database = 'OPER_odc.db' # the DB is located in the same place as the script; also tried with the absolute path
connection_string = "DRIVER=SQLITE3;DATABASE=" + database

Or, if you want to use your DSN (not "DNS"), then just use

connection_string = "DSN=TestDB"

[tatarinova]

Great! :+1: Thank you very much! It work now. Just my remark for other people: no spaces in a connection string (I did not know before), otherwise we have something like:

pyodbc.Error: ('HY000', '[HY000] [unixODBC][SQLite]connect failed (14) (SQLDriverConnect)')