python connect to microsoft sql server db

Question

I am trying to access a microsoft sql server DB trough python but I am receiving Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified')

The only info that I have is the db ip, user, password and db name. I used dbevear to set the connection and it's ok but I need to get data with python. I tried the following:

import pypyodbc 
import pandas as pd

cnxn = pypyodbc.connect(
                        "Server=171.11.111.11;"
                        "Database=database_name;"
                        "uid=my_username;pwd=secret")
df = pd.read_sql_query('select * from db.dtable', cnxn)

I am quite new to python and I don't exactly how to connect to the db. Am I missing something here?

If you want to fill pandas arrays fast you may also want to check out: <github.com/pacman82/arrow-odbc-py>. Full disclosure: I am the author. — Markus Klein
– Markus Klein, Commented Feb 17, 2022 at 14:22

David Browne - Microsoft · Accepted Answer · 2022-02-17 14:27:42Z

1

This

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'

means you failed to specify which ODBC driver you want to use. See eg

import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = 'tcp:myserver.database.windows.net' 
database = 'mydb' 
username = 'myusername' 
password = 'mypassword' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

Connecting to SQL Server using pyodbc

edited Feb 17, 2022 at 14:27

answered Feb 17, 2022 at 14:21

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Markus Klein Over a year ago

Seems you answered the quesition 20s before I did. How do I remove my answer?

Markus Klein Over a year ago

upss, found it.

Collectives™ on Stack Overflow

python connect to microsoft sql server db

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related