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
Advertisement
Answer
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: