I have a python script that requires ODBC Driver 13.1 for SQL Server to connect and write to a SQL Server. I am trying to setup the script on a Linux VM running Ubuntu 16.04 on Azure. I am having trouble locating the location of the driver to use in my script.
I have followed the instructions here on installing ODBC and it appears to have installed successfully. However, whenever I look for where it has installed on my system, I cannot find it.
On my local machine, I am passing the information to the connection string like so:
server = '{server name}'
username = '{username}'
password = '{password}'
driver = '/usr/local/lib/libmsodbcsql.13.dylib'
# use following string for database connection
con = sqlalchemy.create_engine('mssql+pyodbc://' + user + ':' + passw + '@' + host + ':' + str(port) + '/' + database + '?' + 'driver' + '=' + driver, echo=False
On the Linux VM, when I installed ODBC from Microsoft this was a portion of the output
Preparing to unpack .../msodbcsql_13.1.9.2-1_amd64.deb ... Unpacking msodbcsql (13.1.9.2-1) ...
Where is the rest of that file path? When I used find . .../msodbcsql_13.1.9.2-1_amd64.deb it returned no results
In order for the script to function, I need to locate where the driver is on the server so I can pass that path to the script.
Advertisement
Answer
First, please make sure that you have installed unixodbc-dev via apt-get on Azure Ubuntu VM. Then the pymssql or pyodbc module is required by sqlalchemy, you can refer to the offical document Python SQL Driver to install it.
Next, there is my answer for the SO thread Microsoft Azure Data warehouse and SqlAlchemy which you can refer to, and it includes the sample codes as below.
import sqlalchemy connection_string = "mssql+pyodbc://<user>@<server-host>:<password>@<server-host>.database.windows.net:1433/<database>?driver=ODBC+Driver+13+for+SQL+Server" engine = sqlalchemy.engine.create_engine(connection_string) engine.connect()
Or
import sqlalchemy
import urllib
params = urllib.quote_plus("Driver={ODBC Driver 13 for SQL Server};Server=<server-host>.database.windows.net,1433;Database=<database>;Uid=<user>@<server-host>;Pwd=<password>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;")
engine = sqlalchemy.engine.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
engine.connect()
For more details about sqlalchemy with pyodbc or pymssql, please see the SQLAlchemy document for Microsoft SQL Server.