Connecting to Microsoft SQL Server
If you have a paid PythonAnywhere plan, and you have a Microsoft SQL Server database elsewhere on the Internet that you want to connect to (we don't host SQL Server ourselves) then you have two options in terms of Python packages to use.
This is the easiest option. Just connect to it like this:
host = "123.456.789.012" username = "yourusername" password = "yourpassword" database = "yourdatabasename" conn = pymssql.connect(host, username, password, database) cursor = conn.cursor()
appropriately, of course.
This is much trickier to set up, so if you can use
pymssql then we definitely
recommend that option. But if you have a bunch of scripts that already use
pyodbc and need them to work on PythonAnywhere, it is possible.
The aim is to create a ODBC Data Source Name (DSN) called
that your pyodbc code will be able to use to connect to the database. To do this:
Create a new file inside your home directory, called
odbcinst.ini, and containing the following:
[FreeTDS] Description = TDS driver (Sybase/MS SQL) Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so CPTimeout = CPReuse = FileUsage = 1
create another file in your home directory called
.freetds.conf(note the "." at the start), and in it put the following:
[sqlserver] host = YOUR_SQL_SERVER_IP_ADDRESS port = YOUR_SQL_SERVER_PORT tds version = 7.0
YOUR_SQL_SERVER_PORTappropriately, of course.
Create yet another file in your home directory, called
odbc.ini, and put this in it:
[sqlserverdatasource] Driver = FreeTDS Description = ODBC connection via FreeTDS Trace = No Servername = sqlserver
Finally, when you want to connect to the database from your Python code:
import os import pyodbc os.environ["ODBCSYSINI"] = "/home/YOUR_PYTHONANYWHERE_USERNAME" conn = pyodbc.connect('DSN=sqlserverdatasource;Uid=YOUR_SQL_SERVER_USERID;Pwd=YOUR_SQL_SERVER_PASSWORD;Encrypt=yes;Connection Timeout=30;')
YOUR_SQL_SERVER_PASSWORDappropriately. Note: if you're using an Azure-hosted database, you may need to specify both the username and the server name in
YOUR_SQL_SERVER_USERID, separated by an
@. For example,
yourusername@yourservername. See this help page for more information.
Once you've done that, it should all work fine!
If you have more than one SQL server database to connect to
If at a later stage you want to add more DSNs to be able to connect to other
SQL Server instances, you need to add a new block to both
.freetds.conf, just add something identical to the code above, but
square brackets at the start to something different (say,
perhaps something more descriptive), and, of course, change the
parameters appropriately. After that,
.freetds.conf will look something like this:
[sqlserver] host = YOUR_ORIGINAL_SQL_SERVER_IP_ADDRESS port = YOUR_ORIGINAL_SQL_SERVER_PORT tds version = 7.0 [secondsqlserver] host = YOUR_NEW_SQL_SERVER_IP_ADDRESS port = YOUR_NEW_SQL_SERVER_PORT tds version = 7.0
odbc.ini, again, add something identical to the code above, but
secondsqlserverdatasource or something
more descriptive, and then change the
Servername to the name you used in
secondsqlserver. So you'll wind up with something like this:
[sqlserverdatasource] Driver = FreeTDS Description = ODBC connection via FreeTDS Trace = No Servername = sqlserver [secondsqlserverdatasource] Driver = FreeTDS Description = ODBC connection via FreeTDS Trace = No Servername = secondsqlserver
Once you've done that, you should be able to connect to your second SQL Server
database using the same
pyodbc.connect, but changing the value assigned to the
DSN to the one you put in square brackes in
odbc.ini. For example:
conn = pyodbc.connect('DSN=secondsqlserverdatasource;Uid=YOUR_SQL_SERVER_USERID;Pwd=YOUR_SQL_SERVER_PASSWORD;Encrypt=yes;Connection Timeout=30;')