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.
One thing to look out for -- if your SQL Server
instance is on Azure, then your username is "username@databasename". For
example, if your database is
mydatabase.database.windows.net and your username
jim,then the username you should use in the
pymssql.connect would be
jim@mydatabase. See this help page
for more information.
If you have problems connecting to your database, there's a great troubleshooting
guide on the
pymssql site. One
particularly useful trick is to set the
TDSDUMP environment variable, which
makes the system print out loads of debugging data. Check out the link above
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;')
Note: just as with pymssql, if you're using an Azure-hosted database, you 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;')
Some database-in-the-cloud providers expect you to set up a whitelist containing all of the IP addresses that you expect to connect to your database from. This can be problematic because PythonAnywhere code can run on different machines with different IP addresses.