pyodbc

[gmerritt]

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.

Larz60+ write Feb-20-2022, 12:07 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Fixed for you this time. Please use BBcode tags on future posts.

[ndc85430]

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.

[ndc85430]

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

[deanhystad]

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".

[gmerritt]

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...

[gmerritt]

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

[gmerritt]

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.

[gmerritt]

[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.

[gmerritt]

#  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.