3
votes

I am trying to connect to a SQL Database hosted in Windows Azure through MySQLdb with Python.

I keep getting an error mysql_exceptions.OperationalError: (2001, 'Bad connection string.')

This information works when connecting through .NET (vb, C#) but I am definitely not having any luck here.

For below I used my server's name from azure then .database.windows.net Is this the correct way to go about this?

Here is my code:

#!/usr/bin/python
import MySQLdb

conn = MySQLdb.connect(host="<servername>.database.windows.net", user="myUsername", passwd="myPassword", db="db_name")

cursor = conn.cursor()

I have also tried using pyodbc with FreeTDS with no luck.

1
Can you please try by adding server name to your user name? Something like: user="myUsername@<servername>". - Gaurav Mantri
I've added user="myUsername@<servername>" Now it's saying mysql_exception.OperationalError: (2002, 'Internal error.') - Kyle Moffat
Can you try connecting via pymssql? All the examples I have seen makes use of this. - Gaurav Mantri
Using pymssql connection string looks like conn = pymssql.conect(host="<servername>.database.windows.net", user="myUsername", password="myPassword", database="db_name") I get "Operation now in progressDB-Lib error message 20002, severity 9: Adaptive Server connection failed - Kyle Moffat
mysqldb is an interface to MySQL. Azure's SQL Database Service is a SQL Server variant, not a MySQL variant. You need to choose a library that works with SQL Server, not MySQL. And which you choose (such as the one Gaurav suggested) is up to you. Also: if you're going to change library, edit your question - don't put additional detail as comments. Very hard to read, very hard to get the new context of your question. - David Makogon

1 Answers

11
votes

@Kyle Moffat, what OS are you on? Here is how you can use pyodbc on Linux and Windows: https://msdn.microsoft.com/en-us/library/mt763261(v=sql.1).aspx

Windows:

Linux:

  • Open terminal Install Microsoft ODBC Driver 13 for Linux For Ubuntu 15.04 +

     sudo su  
     wget https://gallery.technet.microsoft.com/ODBC-Driver-13-for-Ubuntu-b87369f0/file/154097/2/installodbc.sh  
     sh installodbc.sh  
    
  • For RedHat 6,7

    sudo su
    wget https://gallery.technet.microsoft.com/ODBC-Driver-13-for-SQL-8d067754/file/153653/4/install.sh 
    sh install.sh 
    
  • Install pyodbc

    sudo -H pip install pyodbc
    

Once you install the ODBC driver and pyodbc you can use this Python sample to connect to Azure SQL DB

import pyodbc 
server = 'tcp:myserver.database.windows.net' 
database = 'mydb' 
username = 'myusername' 
password = 'mypassword' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print row[0] 
    row = cursor.fetchone()

If you are not able to install the ODBC Driver you can also try pymssql + FreeTDS

sudo apt-get install python
sudo apt-get --assume-yes install freetds-dev freetds-bin
sudo apt-get --assume-yes install python-dev python-pip
sudo pip install pymssql==2.1.1

Once you follow these steps, you can use the following code sample to connect: https://msdn.microsoft.com/en-us/library/mt715796(v=sql.1).aspx