SQL Alchemy with SQL Server

Question

I wasn't sure what to title my post, if you have a better idea, feel free to edit the title.

I have not used SQL Alchemy before and the documentation that I have looked at located in the following places, is not helpful:

Here is the code I am using:

import sqlalchemy as sal
from sqlalchemy import create_engine
#Here are the parameters I am using:

 - server = 'Q-20/fake_example'
 - database = 'AdventureWorks2017'
 - driver = 'ODBC Driver 17 for SQL Server' 
 - trusted_connection='yes'

DATABASE_CONNECTION = 'mssql+pyodbc://@server = ' + server + '/database = ' + database + '?trusted_connection = ' + trusted_connection + '&driver=' + driver

engine = sal.create_engine(DATABASE_CONNECTION)

All of that seems to work fine without any problems; however, when I add this line:

connection=engine.connect()

I get the following error message:

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]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)')

I am not sure what is wrong with what I am doing, does anyone have any suggestions?

What I have tried so far:

I have confirmed that SQL Server is configured to allow remote connections. I did this check by following the instructions here
Removing the "@" sign before the server, but this just generated the same error message.

https://stackoverflow.com/questions/46085451/building-a-connection-url-for-mssqlpyodbc-with-sqlalchemy-engine-url-url — BENY, May 26 '20 at 00:55
Do the [Hostname Connections](https://docs.sqlalchemy.org/en/13/dialects/mssql.html#hostname-connections) and [Pass through exact Pyodbc string](https://docs.sqlalchemy.org/en/13/dialects/mssql.html#pass-through-exact-pyodbc-string) sections provide any help? — AlwaysLearning, May 26 '20 at 03:18
I edited my post after playing around with it. Progress, but still stuck. I'll take a look though. — Chicken Sandwich No Pickles, May 26 '20 at 03:21
You don't need the `@` symbol before the server name if you're using a trusted connection — Ed Harper, May 26 '20 at 10:16

score 0 · Accepted Answer · answered May 26 '20 at 20:25

I figured out part of what I needed to do. I needed to change my parameters.

Old Parameters:

server = 'Q-20/fake_example'
database = 'AdventureWorks2017'
driver = 'ODBC Driver 17 for SQL Server'
trusted_connection='yes'

New Parameters:

server = 'Q-20'
database = 'AdventureWorks2017'
driver = 'SQL+SERVER+NATIVE+CLIENT+11.0'
trusted_connection='yes'

This is what my code ultimately looked like:

database_connection =  'mssql+pyodbc://Q-20/AdventureWorks2017?trusted_connection=yes&driver=SQL+SERVER+NATIVE+CLIENT+11.0'

SQL Alchemy with SQL Server

1 Answers1