I have successfully connected to a SQL Server 2000 database on a Virtual Machine, via DSN, using a straightforward
conn = pyodbc.connect(f"DSN={DSN}; UID={UName}; PWD={Pwd}")
However, when trying to build a Pandas dataframe from a query on the database I get
UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection
I have tried MANY different ways of creating a SQLAlchemy connection and just can't get it to work.
First attempt was to follow the simple example at https://docs.sqlalchemy.org/en/20/dialects/mssql.html#dsn-connections - engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")
| string | error |
|---|---|
| mssql+pyodbc://UName:Pwd@dsn_name | 'schema_name' is not a recognized function name. (195) (SQLExecDirectW) |
| Then specifying drivers: | |
| mssql+pyodbc://UName:Pwd@dsn_name/?driver=ODBC+Driver+17+for+SQL+Server | 'schema_name' is not a recognized function name. (195) (SQLExecDirectW) |
| mssql+pyodbc://UName:Pwd@dsn_name/?driver=SQL+Server | 'schema_name' is not a recognized function name. (195) (SQLExecDirectW) |
| Then with the database name but no driver: | |
| mssql+pyodbc://UName:Pwd@dsn_name/db | Data source name not found and no default driver specified (0) (SQLDriverConnect) |
| Then with both the database name and driver: | |
| mssql+pyodbc://UName:Pwd@dsn_name/db?driver=ODBC+Driver+17+for+SQL+Server | Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) Login timeout expired (0) A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53) |
| mssql+pyodbc://UName:Pwd@dsn_name/db?driver=SQL+Server | SQL Server does not exist or access denied. (17) ConnectionOpen (Connect()). (53) |
After that, I tried using various combinations of a URL-formatted connection string. My default connection string is built around:
connection_url = URL.create(
"mssql+pyodbc",
username=UName,
password=Pwd,
host=server_name,
database=db,
query={"driver": odbc_driver},
)
The database name is saved in the DSN and works fine for the pyodbc.connect connection.
Variations I have tried:
| host | odbc_driver | Outcome |
|---|---|---|
| server_name | ODBC Driver 18 for SQL Server | ODBC Driver 18 for SQL Server does not support connections to SQL Server 2000 or earlier versions. (22) Client unable to establish connection (22) |
| server_name | SQL Server | 'schema_name' is not a recognized function name |
| server_name | (none) | Data source name not found and no default driver specified (0) |
| dsn_name | ODBC Driver 18 for SQL Server | Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect) Login timeout expired (0) A network-related or instance-specific error has occurred while establishing a connection to . Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections |
| dsn_name | SQL Server | SQL Server does not exist or access denied. (17) ConnectionOpen (Connect()). (53) |
| dsn_name | (none) | Data source name not found and no default driver specified (0) |
The errors occur when trying to connect the engine, at the line: with engine.connect() as connection. I have also tried pymssql as an alternate dialect instead of pyodbc
I can successfully ping the Server and get a perfectly fine response:
Pinging server_name [192.168.x.y] with 32 bytes of data:
Reply from 192.168.x.y: bytes=32 time<1ms TTL=128
Reply from 192.168.x.y: bytes=32 time=2ms TTL=128
Reply from 192.168.x.y: bytes=32 time=4ms TTL=128
Reply from 192.168.x.y: bytes=32 time=4ms TTL=128
Ping statistics for 192.168.x.y:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 4ms, Average = 2ms
Unfortunately, I can't find any suitable ODBC drivers - the oldest I can find is ODBC Driver 11 for SQL Server and that gives me the same errors as 18.
DSN Authentication is set to SQL Server authentication using a login ID and password
Test of the connection:
Microsoft SQL Server ODBC Driver Version 10.00.19041
Data Source Name: dsn_name
Data Source Description: dsn_description
Server: server_name
Database: db
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No
Python 3.11.5
SQLAlchemy 2.0.15
pandas 2.0.1
pyodbc 4.0.39