Connect Python with SQL Server Database

Question

When I am trying to connect python with SQL Server, following error occurred.

"pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)')"

Following is the my code.

import pyodbc
connection = pyodbc.connect("Driver={SQL Server}; Server=localhost;          
Database=emotionDetection; uid=uname ;pwd=pw;Trusted_Connection=yes")
cursor = connection.cursor()
SQLCommand = ("INSERT INTO emotion"  "(happy, sad, angry) "
          "VALUES (?,?,?)")
Values = ['smile','cry','blame']
cursor.execute(SQLCommand,Values)
connection.commit()
connection.close()

This is my first attempt to connect Python with sql server. I don't have an idea what would be the driver name, server name, username and password.Do you have any idea of what should be my configuration. Please help me.

`Server='.\SQLEXPRESS` did you try this ? – Sudheesh Singanamalla Feb 24 '17 at 08:04 — Sudheesh Singanamalla, Feb 24 '17 at 08:04
Yes, but still happen same error. – Chathurika Feb 24 '17 at 08:07 — Chathurika, Feb 24 '17 at 08:07

score 9 · Answer 1 · edited Jun 20 '20 at 09:12

9

CONNECTION FROM WINDOWS TO MS SQL SERVER DATABASE:

Here you have an example I use myself to connect to MS SQL database table with a Python script:

import pyodbc
server = 'ip_database_server'
database = 'database_name'
username = 'user_name'
password = 'user_password'
driver = '{SQL Server}' # Driver you need to connect to the database
port = '1433'
cnn = pyodbc.connect('DRIVER='+driver+';PORT=port;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+
                 ';PWD='+password)
cursor = cnn.cursor()

'User' and 'password' and 'table_name' are attibutes defined by the DB administrator, and he should give them to you. The port to connect to is also defined by the admin. If you are trying to connect from a Windows device to the DB, go to ODBC Data Source Administrator from Windows, and check if you have installed the driver:

Where is the ODBC data source administrator in a Windows machine.

The image is in spanish, but you only have to click on 'Drivers' tab, and check if the driver is there as in the image.

CONNECTION FROM LINUX/UNIX TO MS SQL SERVER DATABASE:

If you are working in Linux/Unix, then you shoud install a ODBC manager like 'FreeTDS' and 'unixODBC'. To configure them, you have some examples in the following links:

Example: Connecting to Microsoft SQL Server from Linux/Unix

Example: Installing and Configuring ODBC

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 24 '17 at 08:34

BSP

735
1
10
27

Thank you in advance. How can I find the username and password? What should be the username and password. – Chathurika Feb 24 '17 at 11:47
I checked the data source and SQL Server Driver is there. But still having the same problem. How can I fix it further. – Chathurika Feb 24 '17 at 12:28
@Chathurika Are you the administrator of the database? I mean the one who has created it. If you are not, then you have to ask him or her for the `user name`, `password`, `table name`, `server ip` and so on, to have access to it. If you created the database, then you should have the information somewhere. – BSP Feb 24 '17 at 12:43
Yes I am the administrator of the database. But I didn't put any uname or pw to the DB. – Chathurika Feb 24 '17 at 14:30
check the port you are using then, maybe is on different from 1433 – BSP Feb 27 '17 at 10:51
This is working for me but only for default instances. When I am trying to connect a named instance it is giving me error. I tried to put ```server_name\instance_name``` as well – EXODIA May 07 '20 at 08:51

score 2 · Answer 2 · edited May 23 '17 at 11:53

2

I think you should check out this. stackoverflow answer about odbc

Also, what sql server do you use?

edited May 23 '17 at 11:53

Community

1
1

answered Feb 24 '17 at 08:01

june song

31
2

I am using SQL Server 2014 – Chathurika Feb 24 '17 at 08:02

Shubh · Answer 3 · 2019-01-28T10:54:28.993

1

The library pymssql doesnot require any drivers and works on both Windows as well as Ubunutu.

import pymssql
import pandas as pd

server = 'yourusername'
username = 'yourusername'
password = 'yourpassword'
database = 'yourdatabase'
table_name = 'yourtablename'
conn = pymssql.connect(host=server,user=username,password=password,database=database)
dat = pd.read_sql("select * from table_name,conn)

edited Jan 28 '19 at 10:54

answered Jan 28 '19 at 09:38

Shubh

585
9
29

Holy cow, that was so easy! I only needed to select some data and didn't want to have users configure ODBC drivers, especially across OS's. The code above worked perfectly to read from an Azure SQL DB without any other outside configuration or setup. Where has this been my whole life?! – Zach Mierzejewski May 11 '21 at 18:03

score 1 · Answer 4 · answered Jul 16 '19 at 11:03

Try pyodbc with SQLalchemy

try this:

import sqlalchemy
import pyodbc
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://user:password@host:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")

cnxn = engine.connect()

Use your corresponding driver

It works for me

Luck!

score 0 · Answer 5 · answered Aug 01 '18 at 07:42

Working Examples Work Best For Me:

Need Mac ODBC Drivers?

If you need the mac driver I used homebrew and found the commands here

Detail

I personally learn best by reverse enginerring, with that said I am sharing one of my examples, it may be a bit crude but I'm growing my Python skills.

My script I created allows me to connect my Mac OS to a AWS RDS instance.

The whole script is a copy paste with a little modification for you about your server info, and you are off and running. just modify these lines to connect.

server = 'yourusername'
username = 'yourusername'
password = 'yourforgottencomplicatedpassword'
database = 'yourdatabase'

Then Run the file: python3 ~/Your/path/pyodbc_mssqldbtest.py and you should be set.

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# =============================================================================
# Created By  : Jeromie Kirchoff
# Created Date: Mon July 31 22:32:00 PDT 2018
# FILENAME: pyodbc_mssqldbtest.py
# =============================================================================
"""The Module Has Been Build for Interaction with MSSQL DBs To Test the con."""
# =============================================================================
# Thanks to this post for headers https://stackoverflow.com/q/12704305/1896134
# Answer to an SO question: https://stackoverflow.com/q/42433408/1896134
# =============================================================================

import pyodbc


def runningwithqueries(query):
    """The Module Has Been Build to {Open, Run & Close} query connection."""
    print("\nRunning Query: " + str(query) + "\nResult :\n")
    crsr = cnxn.execute(query)
    columns = [column[0] for column in crsr.description]
    print(columns)
    for row in crsr.fetchall():
        print(row)
    crsr.close()

# =============================================================================
# SET VARIABLES NEEDED FOR SERVER CONNECTION
# =============================================================================
server = 'yourusername'
username = 'yourusername'
password = 'yourforgottencomplicatedpassword'
database = 'yourdatabase'

connStr = (r'DRIVER={ODBC Driver 17 for SQL Server};' +
           r"Integrated Security=True;" +
           r'SERVER=' + server +
           r';UID=' + username +
           r';PWD=' + password +
           r';DSN=MSSQL-PYTHON' +
           r';DATABASE=' + database + ';'
           )

print("Your Connection String:\n" + str(connStr) + "\n\n")

# =============================================================================
# CONNECT TO THE DB
# =============================================================================
cnxn = pyodbc.connect(connStr, autocommit=True)

# =============================================================================
# SET QUERIES TO VARIABLES
# =============================================================================
SQLQUERY1 = ("SELECT @@VERSION;")
SQLQUERY2 = ("SELECT * FROM sys.schemas;")
SQLQUERY3 = ("SELECT * FROM INFORMATION_SCHEMA.TABLES;")
SQLQUERY4 = ("SELECT * FROM INFORMATION_SCHEMA.COLUMNS;")
SQLQUERY5 = ("SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;")
SQLQUERY6 = ("EXEC sp_databases;")
SQLQUERY7 = ("EXEC sp_who2 'active';")

# =============================================================================
# RUN QUERIES
# YOU CAN RUN AS MANY QUERIES AS LONG AS THE CONNECTION IS OPEN TO THE DB
# =============================================================================
runningwithqueries(SQLQUERY1)
runningwithqueries(SQLQUERY2)
runningwithqueries(SQLQUERY3)
runningwithqueries(SQLQUERY4)
runningwithqueries(SQLQUERY5)
runningwithqueries(SQLQUERY6)
runningwithqueries(SQLQUERY7)

# =============================================================================
# CLOSE THE CONNECTION TO THE DB
# =============================================================================
cnxn.close()

score 0 · Answer 6 · answered Feb 19 '20 at 06:54

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};' 'Server=**SERVER NAME**;' 'Database=**DATABASE NAME**;' 'Trusted_Connection=yes;') 
cursor = conn.cursor() 

cursor.execute('SELECT * FROM Output3')

This works just check you specify the Respective Driver, Server and the Database names correctively!