How to connect to a Azure SQL Server through Python

Question

I've come across some trouble accessing my SQL Server through Python. I can get it going when my SQL Server is installed locally, but not when it is online.

I have used the following code:

Connection to SQL Server

params = urllib.parse.quote_plus(r'Driver={ODBC Driver 13 for SQL Server};Server=tcp:xxxx.database.windows.net,1433;Database=xxxx;Uid=xxxx;Pwd=xxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine = create_engine(conn_str)

but then when I try and run:

df.to_sql(name='xxxx',con=engine, if_exists='replace',index=False)

I get the error:

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I can see there is something to do with the driver however I access both a local SQL Server DB (which the above code works for) and the Azure SQL.

Any help would be greatly appreciated! Thanks

Answer 1

Have you tried pyodbc that way?

/.env/db.conf file with connection details:

[azure_conn]
driver: {ODBC Driver 17 for SQL Server}
server: server_name.database.windows.net
port: 1433
database: database_name
uid: admin
pwd: password

Connection and retrieving data:

import pyodbc, configparser

section_name = 'azure_conn'
config = configparser.ConfigParser()
c = config.read("./.env/db.conf")
db_opts = config.options(section_name)
details = dict()

for db_opt in db_opts:
    details[db_opt] = config.get(section_name, db_opt)

connect_string = 'DRIVER={driver};SERVER={server};PORT={port};DATABASE={database};UID={uid};PWD={pwd}'.format(**details)

try:
    connection = pyodbc.connect(connect_string)
except pyodbc.Error as ex:
    sqlstate = ex.args[1]
    print(sqlstate)
else:
    print(connection)
    cursor = connection.cursor()
    cursor.execute("select @@version as ver")
    rows = cursor.fetchall()
    for row in rows:
        print(row.ver)
    connection.close()

Also please refer to this Docs.MS page.

EDIT#1:

I have tried using pandas DataFrame and sqlalchemy:

from sqlalchemy import create_engine
import urllib, configparser
import pandas as pd

section_name = 'azure_conn'
config = configparser.ConfigParser()
c = config.read("./.env/db.conf")
db_opts = config.options(section_name)
details = dict()

for db_opt in db_opts:
    details[db_opt] = config.get(section_name, db_opt)

connect_string = 'DRIVER={driver};SERVER={server};PORT={port};DATABASE={database};UID={uid};PWD={pwd}'.format(**details)

params = urllib.parse.quote_plus(connect_string)

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})

df.to_sql('users', con=engine)

engine.execute("SELECT * FROM users").fetchall()

Output was:

[(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]

I checked Azure portal, table Users was created and has 3 rows.

I've tried:

df.to_sql('users', con=engine, if_exists='replace', index=False)

Output:

[('User 1',), ('User 2',), ('User 3',)]

Azure portal: