Connecting to Microsoft SQL server using Python
PythonSqlWindowsPython Problem Overview
I am trying to connect to SQL through python to run some queries on some SQL databases on Microsoft SQL server. From my research online and on this forum the most promising library seems to be pyodbc. So I have made the following code
import pyodbc
conn = pyodbc.connect(init_string="driver={SQLOLEDB}; server=+ServerName+;
database=+MSQLDatabase+; trusted_connection=true")
cursor = conn.cursor()
and get the following error
Traceback (most recent call last):
File "C:\Users...\scrap.py", line 3, in <module>
conn = pyodbc.connect(init_string="driver={SQLOLEDB}; server=+ServerName+; database=+MSQLDatabase+; trusted_connection=true")
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
I have looked at the folowing posts and tried changing my driver to {sql server} and have connected using ODBC links before in SAS, which is partially what my above code is based on, so don't think I need to install anything else.
Thanks
Python Solutions
Solution 1 - Python
This is how I do it...
import pyodbc
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=server_name;"
"Database=db_name;"
"Trusted_Connection=yes;")
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Table')
for row in cursor:
print('row = %r' % (row,))
Relevant resources:
Solution 2 - Python
Minor addition to what has been said before. You likely want to return a dataframe. This would be done as
import pypyodbc
import pandas as pd
cnxn = pypyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=server_name;"
"Database=db_name;"
"uid=User;pwd=password")
df = pd.read_sql_query('select * from table', cnxn)
Solution 3 - Python
In data source connections between a client and server there are two general types: ODBC which uses a DRIVER and OLEDB which uses a PROVIDER. And in the programming world, it is a regular debate as to which route to go in connecting to data sources.
You are using a provider, SQLOLEDB, but specifying it as a driver. As far as I know, neither the pyodbc nor pypyodbc modules support Window OLEDB connections. However, the adodbapi does which uses the Microsoft ADO as an underlying component.
Below are both approaches for your connection parameters. Also, I string format your variables as your concatenation did not properly break quotes within string. You'll notice I double the curly braces since it is needed in connection string and string.format() also uses it.
# PROVIDER
import adodbapi
conn = adodbapi.connect("PROVIDER=SQLOLEDB;Data Source={0};Database={1}; \
trusted_connection=yes;UID={2};PWD={3};".format(ServerName,MSQLDatabase,username,password))
cursor = conn.cursor()
# DRIVER
import pyodbc
conn = pyodbc.connect("DRIVER={{SQL Server}};SERVER={0}; database={1}; \
trusted_connection=yes;UID={2};PWD={3}".format(ServerName,MSQLDatabase,username,password))
cursor = conn.cursor()
Solution 4 - Python
I Prefer this way ... it was much easier
http://www.pymssql.org/en/stable/pymssql_examples.html
conn = pymssql.connect("192.168.10.198", "odoo", "secret", "EFACTURA")
cursor = conn.cursor()
cursor.execute('SELECT * FROM usuario')
Solution 5 - Python
Following Python code worked for me. To check the ODBC connection, I first created a 4 line C# console application as listed below.
Python Code
import pandas as pd
import pyodbc
cnxn = pyodbc.connect("Driver={SQL Server};Server=serverName;UID=UserName;PWD=Password;Database=My_DW;")
df = pd.read_sql_query('select TOP 10 * from dbo.Table WHERE Patient_Key > 1000', cnxn)
df.head()
Calling a Stored Procedure
dfProcResult = pd.read_sql_query('exec dbo.usp_GetPatientProfile ?', cnxn, params=['MyParam'] )
C# Program to Check ODBC Connection
static void Main(string[] args)
{
string connectionString = "Driver={SQL Server};Server=serverName;UID=UserName;PWD=Password;Database=My_DW;";
OdbcConnection cn = new OdbcConnection(connectionString);
cn.Open();
cn.Close();
}
Solution 6 - Python
Try using pytds, it works throughout more complexity environment than pyodbc and more easier to setup.
I made it work on Ubuntu 18.04
> Ref: https://github.com/denisenkom/pytds
Example code in documentation:
import pytds
with pytds.connect('server', 'database', 'user', 'password') as conn:
with conn.cursor() as cur:
cur.execute("select 1")
cur.fetchall()
Solution 7 - Python
Try with pymssql: pip install pymssql
import pymssql
try:
conn = pymssql.connect(server="host_or_ip", user="your_username", password="your_password", database="your_db")
cursor = conn.cursor()
cursor.execute ("SELECT @@VERSION")
row = cursor.fetchone()
print(f"\n\nSERVER VERSION:\n\n{row[0]}")
cursor.close()
conn.close()
except Exception:
print("\nERROR: Unable to connect to the server.")
exit(-1)
Output:
SERVER VERSION:
Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64)
Jul 31 2020 18:47:07
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
The connection can also be checked from the terminal, with a single line of code with sqlcmd. See syntax.
╔═════════╦═════════════════════════════════════════╗
║ Command ║ Description ║
╠═════════╬═════════════════════════════════════════╣
║ -S ║ [protocol:]server[instance_name][,port] ║
║ -U ║ login_id ║
║ -p ║ password ║
║ -Q ║ "cmdline query" (and exit) ║
╚═════════╩═════════════════════════════════════════╝
sqlcmd -S "host_or_ip" -U "your_username" -p -Q "SELECT @@VERSION"
output:
Password: your_password
--------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64)
Jul 31 2020 18:47:07
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
(1 rows affected)
Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total 1 avg 1.00 (1000.00 xacts per sec.)
Solution 8 - Python
I tried to connect sql server in following ways and those worked for me.
To connect using windows authentication
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};Server='+servername+';Trusted_Connection=yes;Database='+databasename+';')
cursor = conn.cursor()
cursor.execute("Select 1 as Data")
To use sql server authentication I used following code.
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};Server='+servername+ ';UID='+userid+';PWD='+password+';Database='+databasename)
cursor1 = conn.cursor()
cursor1.execute("SELECT 1 AS DATA")
Solution 9 - Python
here's the one that works for me:
from sqlalchemy import create_engine
import urllib
conn_str = (
r'Driver=ODBC Driver 13 for SQL Server;'
r'Server=DefinitelyNotProd;'
r'Database=PlayPen;'
r'Trusted_Connection=Yes;')
quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
Solution 10 - Python
An alternative approach would be installing Microsoft ODBC Driver 13, then replace SQLOLEDB with ODBC Driver 13 for SQL Server
Regards.
Solution 11 - Python
I found up-to-date resources here: Microsoft | SQL Docs | Python SQL Driver
There are these two options explained including all the prerequisites needed and code examples: Python SQL driver - pyodbc (tested & working) Python SQL driver - pymssql
Solution 12 - Python
My version. Hope it helps.
import pandas.io.sql
import pyodbc
import sys
server = 'example'
db = 'NORTHWND'
db2 = 'example'
#Crear la conexión
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server +
';DATABASE=' + db +
';DATABASE=' + db2 +
';Trusted_Connection=yes')
#Query db
sql = """SELECT [EmployeeID]
,[LastName]
,[FirstName]
,[Title]
,[TitleOfCourtesy]
,[BirthDate]
,[HireDate]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[HomePhone]
,[Extension]
,[Photo]
,[Notes]
,[ReportsTo]
,[PhotoPath]
FROM [NORTHWND].[dbo].[Employees] """
data_frame = pd.read_sql(sql, conn)
data_frame