Connecting to Microsoft SQL server using Python

Question

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  lt module gt      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   pyodbc Error     39 IM002  39     39  IM002   unixODBC  Driver Manager Data source name not found  and no default driver specified  0   SQLDriverConnect   39    Pyodbc -  quot Data source name not found  and no default driver specified quot   Thanks

User · Answer

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

User · Answer

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 RCO DW    df   pd read sql query  select TOP 10   from dbo Table WHERE Patient Key  gt  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 RCO DW            OdbcConnection cn   new OdbcConnection connectionString           cn Open            cn Close

User · Answer

An alternative approach would be installing Microsoft ODBC Driver 13  then replace SQLOLEDB with ODBC Driver 13 for SQL Server  Regards

User · Answer

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

User · Answer

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    https   github com mkleehammer pyodbc wiki Connecting-to-SQL-Server-from-Windows http   blogs msdn com b cdndevs archive 2015 03 11 python-and-data-sql-server-as-a-data-source-for-python-applications aspx

User · Answer

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  amp  working  Python SQL driver - pymssql

User · Answer

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

User · Answer

Try with pymssql  pip install pymssql import pymssql  try      conn   pymssql connect server  quot host or ip quot   user  quot your username quot   password  quot your password quot   database  quot your db quot       cursor   conn cursor       cursor execute   quot SELECT   VERSION quot       row   cursor fetchone       print f quot  n nSERVER VERSION  n n row 0   quot       cursor close       conn close   except Exception      print  quot  nERROR  Unable to connect to the server  quot       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  lt X64 gt   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        quot cmdline query quot   and exit                   ---------------------------------------------------   sqlcmd -S  quot host or ip quot   -U  quot your username quot  -p -Q  quot SELECT   VERSION quot   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  lt X64 gt   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[python] Connecting to Microsoft SQL server using Python

Examples related to python

Examples related to sql

Examples related to windows