Python Forum

A Database was created in SqliteStudio 3.3.3 and is working 100,000 +/- records.

The following python connection string fails :

import pyodbc

con = pyodbc.connect(
    "DRIVER={SQLite3 ODBC Driver};SERVER=localhost;Database=C:\Users\GM\CALL_SIGN_DATABASE1.db;Trusted_connection=yes")

Error message:

Error:
 File "C:\Users\Garth Merritt\PycharmProjects\main\SQL.py", line 5
    "DRIVER={SQLite3 ODBC Driver};SERVER=localhost;Database=C:\Users\GM\CALL_SIGN_DATABASE1.db;Trusted_connection=yes")
                                                                                                                    ^
SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 57-58: truncated \UXXXXXXXX escape

Is there an error in the connection string ?
Is pyodbc able to connect to SQLiteStudio database ?

Thank you for your comments.

Remember that \ followed by a character is considered to be an escape sequence. If you want literal slashes in the string, you need to escape them (i.e. write \\ instead), or use a raw string.

Also, out if interest, why use this ODBC module to connect to the database, instead of the built-in sqlite3 one?

I wanted to see what unicode characters are at positions 57-58. To disable treating "\" as part of an escape sequence I used the "r" prefix to make this a "raw" string.

Output:
>>> x = r"DRIVER={SQLite3 ODBC Driver};SERVER=localhost;Database=C:\Users\GM\CALL_SIGN_DATABASE1.db;Trusted_connection=yes"
This modifies the str, replacing "\" with "\\".  This is how you tell Python that a backslash is just a backslash and not an escape sequence.
'DRIVER={SQLite3 ODBC Driver};SERVER=localhost;Database=C:\\Users\\GM\\CALL_SIGN_DATABASE1.db;Trusted_connection=yes'
>>> x[56:]
':\\Users\\GM\\CALL_SIGN_DATABASE1.db;Trusted_connection=yes'

x[57] is the backslash just before "Users".

(Feb-20-2022, 03:05 PM)deanhystad Wrote: [ -> ]I wanted to see what unicode characters are at positions 57-58. To disable treating "\" as part of an escape sequence I used the "r" prefix to make this a "raw" string.

Output:
>>> x = r"DRIVER={SQLite3 ODBC Driver};SERVER=localhost;Database=C:\Users\GM\CALL_SIGN_DATABASE1.db;Trusted_connection=yes"
This modifies the str, replacing "\" with "\\".  This is how you tell Python that a backslash is just a backslash and not an escape sequence.
'DRIVER={SQLite3 ODBC Driver};SERVER=localhost;Database=C:\\Users\\GM\\CALL_SIGN_DATABASE1.db;Trusted_connection=yes'
>>> x[56:]
':\\Users\\GM\\CALL_SIGN_DATABASE1.db;Trusted_connection=yes'

x[57] is the backslash just before "Users".

Thank you...

(Feb-20-2022, 12:34 AM)gmerritt Wrote: [ -> ]A Database was created in SqliteStudio 3.3.3 and is working 100,000 +/- records.

The following python connection string fails :

import pyodbc

con = pyodbc.connect(
    "DRIVER={SQLite3 ODBC Driver};SERVER=localhost;Database=C:\Users\GM\CALL_SIGN_DATABASE1.db;Trusted_connection=yes")

Error message:

Error:
 File "C:\Users\Garth Merritt\PycharmProjects\main\SQL.py", line 5
    "DRIVER={SQLite3 ODBC Driver};SERVER=localhost;Database=C:\Users\GM\CALL_SIGN_DATABASE1.db;Trusted_connection=yes")
                                                                                                                    ^
SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 57-58: truncated \UXXXXXXXX escape

Is there an error in the connection string ?
Is pyodbc able to connect to SQLiteStudio database ?

Thank you for your comments.

Thank you

(Feb-20-2022, 05:56 AM)ndc85430 Wrote: [ -> ]Also, out if interest, why use this ODBC module to connect to the database, instead of the built-in sqlite3 one?

I have a working version using Sqlite3, but was wondering how the SQL query execution time would vary using an ODBC connection.

[quote="gmerritt" pid='153743' dateline='1645452287']
[quote="deanhystad" pid='153693' dateline='1645369513']

import pyodbc
import pandas as pd
from sqlalchemy import create_engine

CONN = pyodbc.connect(
    "DRIVER={SQLite3 ODBC Driver};SERVER=localhost;DATABASE=C:\\Users\\DB\\Desktop\\CALLDB.db;Trusted_connection=yes")
cnx = create_engine('sqlite:///CALLDB.db').connect()
# table named 'CALL' will be returned as a dataframe.
df = pd.read_sql_table('CALL', cnx)
print(df)

Thank you for the feedback on the syntax in the connection string. The above code works now.

#  print in a grid
import pandas as pd
from sqlalchemy import create_engine

# SQLAlchemy connectable
cnx = create_engine('sqlite:///CALLDB.db').connect()

# table named 'contacts' will be returned as a dataframe.
df = pd.read_sql_table('CALL', cnx)
print(df)

The above connection string works well.