We use SQLAlchemy at Zapier to handle external database connections to things like MySQL, MSSQL, PostgreSQL, and others. For example, you could set up a Zap to add a new row into your database table every time someone tweets a specific word. Or archive tickets from JIRA into your in-house database.
We ran into a problem with our MSSQL connector recently. If you were using a username or password with a plus sign (+) in it, you could not connect. A quick search leads us to this solution. This works well to properly URL encode the plus sign when you are using standard built in SQLAlchemy connection strings (to MySQL, for example).
In our case, we are running SQLAlchemy on Ubuntu and need to rig up FreeTDS and PyODBC. You must use a special SQLAlchemy connection string to pass along extra variables, shown here:
mssql+pyodbc:///?odbc_connect=dsn%3Dmydsn%3BDatabase%3Ddb
Notice that the actual variables are encoded into the odbc_connect query parameter. Naturally, I encoded my username and password into the odbc_connect parameter. The problem is that the odbc_connect parameter gets decoded twice. Normally SQLAlchemy connection strings get decoded once. The "quick answer" article above alludes to this. The second decode happens because the odbc_connect parameter has its own data structure (dsn=mydsn;Database=db). Presumably the odbc_connect parameter was added at a later time without consider how odd it is to double URL encode things.
About that plus sign... A plus sign "+" is encoded as "%2B". Decoded gives you back "+". Decoding a second time gives you a space " " (by definition of the URL encoding scheme for plus signs which are treated specially). So the proper way to handle this it to URL encode the odbc_connect parameter twice! Plus signs "+" end up as "%252B". This results in an incorrect password being sent to the server.
Here is the proper, working code to generate a mssql+pyodbc SQLAlchemy connection:
fields = {
'host': 'database.host.com'
'username': 'myusername'
'password': 'password+with+a+plus!'
'port': '1433'
}
db_url = 'mssql+pyodbc:///?odbc_connect=' + urllib.quote_plus(urllib.quote_plus('DRIVER={{FreeTDS}};SERVER={host};DATABASE={database};UID={username};PWD={password};port={port};TDS_Version=8.0;'.format(fields)
connection = create_engine(db_url, connect_args={'convert_unicode': True})
### About the "API Quirks" Blog Series
Everyone at Zapier has to deal with API Quirks. We deal with so many APIs each day, we have become almost immune to the often bizarre, undocumented, frustrating behavior exhibited by web APIs. No API or documentation is static. Take any quirks published before "today" with a grain of salt.
Don't want to worry about this? Connect to MSSQL with Zapier
