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?

• Markus Klein: 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.

Answer 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

• Markus Klein: Seems you answered the quesition 20s before I did. How do I remove my answer?
• Markus Klein: upss, found it.