-
Notifications
You must be signed in to change notification settings - Fork 564
Tips and Tricks by Database Platform
The following is a collection of tips for working with specific database platforms. For additional tips that apply to pyodbc as a whole, see Features beyond the DB API.
Microsoft Access | Microsoft SQL Server
pyodbc can work with Access databases that contain ODBC Linked Tables if we disable connection pooling before connecting to the Access database:
import pyodbc
pyodbc.pooling = False
cnxn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ= ... ")
Access DDL does not support CREATE DATABASE
(or similar). If you need to create a new, empty database file you can use the (free) third-party msaccessdb module.
The Access ODBC driver is not able to fully support some of the "complex" column types like "(multi-valued) Lookup" fields and "Attachment" fields. In some cases the ODBC driver can read values from such a column, but is unable to perform INSERT/UPDATE/DELETE operations. If you have to perform such tasks then you may need to use Access DAO (Data Access Objects), perhaps in conjunction with IronPython or Python for .NET.
There is a list of more general Microsoft Access specifications and limitations here.
See the Calling Stored Procedures page for an example of how to use a bit of T-SQL to retrieve these values.
Microsoft's SQL Server ODBC Driver for Linux is unable to resolve SQL Server instance names. However, if the SQL Browser service is running on the target machine we can use the (free) third-party sqlserverport module to look up the TCP port based on the instance name.
Use an Output Converter function to retrieve such values. See the examples on the Using an Output Converter function wiki page.
Query parameters for DATETIMEOFFSET columns currently must be sent as strings. Note that SQL Server is rather fussy about the format of the string:
# sample data
dto = datetime(2018, 8, 2, 0, 28, 12, tzinfo=timezone(timedelta(hours=-6)))
dto_string = dto.strftime("%Y-%m-%d %H:%M:%S %z") # 2018-08-02 00:28:12 -0600
# Trying to use the above will fail with
# "Conversion failed when converting date and/or time from character string."
# We need to add the colon for SQL Server to accept it
dto_string = dto_string[:23] + ":" + dto_string[23:] # 2018-08-02 00:28:12 -06:00
Python's decimal.Decimal type can represent floating point numbers with greater than 35 digits of precision, which is the maximum supported by SQL server. Binding parameters that exceed this precision will result in an invalid precision error from the driver ("HY104 [Microsoft][...]Invalid precision value").
fast_executemany
can have difficulty identifying the column types of a local #temporary table under some circumstances (#295).
Use "ODBC Driver 17 for SQL Server" (or newer) and include ColumnEncryption=Enabled
in the connection string, e.g.,
cnxn_str = (
"Driver=ODBC Driver 17 for SQL Server;"
"Server=192.168.1.144,49242;"
"UID=sa;PWD=whatever;"
"Database=myDb;"
"ColumnEncryption=Enabled;"
)
Upgrade to pyodbc 4.0.24 (or newer) and use setinputsizes
to specify the parameter type, etc..
crsr.execute("""\
CREATE TABLE #issue295 (
id INT IDENTITY PRIMARY KEY,
txt NVARCHAR(50),
dec DECIMAL(18,4)
)""")
sql = "INSERT INTO #issue295 (txt, dec) VALUES (?, ?)"
params = [('Ώπα', 3.141)]
# explicitly set parameter type/size/precision
crsr.setinputsizes([(pyodbc.SQL_WVARCHAR, 50, 0), (pyodbc.SQL_DECIMAL, 18, 4)])
crsr.fast_executemany = True
crsr.executemany(sql, params)
If neither of the previous workarounds is feasible, simply use a global ##temporary table instead of a local #temporary table.