confirm "database" string argument works with pymssql

[Daniel Halperin]

SQL Azure does not support the USE statement -- the database must be set at connection create time.

I verified that this is handled correctly by pymssql when invoked directly.

However, it looks like SQLAlchemy does not support this behavior.

[Randy Syring]

A little more info from the pymssql docs:

http://www.pymssql.org/en/latest/ref/pymssql.html#pymssql.connect

including note about Azure.

[Mike Bayer]

can you confirm "USE" only refers to the workaround you've been using? I dont see "USE" emitted from SQLAlchemy.

it would appear this is a feature add to support a newly added option in pymssql? you can of course create usernames in SQL Server that refer to the desired database directly.

[Daniel Halperin]

Mike: I don't quite understand your question, but here are some partial answers

1. SQL Alchemy uses the USE statement (albeit in its lowercase form): https://bitbucket.org/zzzeek/sqlalchemy/src/32165f50209036a98959553e1c5e81537a091a15/lib/sqlalchemy/dialects/mssql/base.py?at=master#cl-1308

2. SQL Azure does not support USE: http://msdn.microsoft.com/en-us/library/ee336288.aspx

In Microsoft Microsoft Azure SQL Database, the database parameter can only refer to the current database. The USE statement does not switch between databases. To change databases, you must directly connect to the database.

[Mike Bayer]

the aforementioned "use" is necessary in exactly one very rare case: when you specify schema="somedb.someowner", that is, a schemaname that itself has a dot in it and therefore intends to select from tables across databases, and you use reflection features, which includes the "has table" feature (e.g. metadata.create_all() without specifying checkfirst=False). This form is not for general use and if azure doesn't support "USE" or equivalent, it can't be used. Please confirm you are attempting to use reflection with a dotted schema name and that is the source of the "use" statement, or otherwise please provide a full test case that illustrates where "USE" is coming from.

in the vast majority of cases, a SQL connection should be dealing with just one database.

The "database" parameter should be sent, my previous statement was inaccurate:

from sqlalchemy.dialects.mssql import pymssql
from sqlalchemy.engine import url

u = url.make_url("mysql+pymssql://scott:tiger@hostname/dbname")
print(pymssql.MSDialect_pymssql().create_connect_args(u))

output:

[[], {'host': 'hostname', 'password': 'tiger', 'user': 'scott', 'database': 'dbname'}]

[Daniel Halperin]

Confirm that this works if we use pymssql from git. The pymssql in pip is broken.

Thanks. Sorry for the confusion, this either changed in the last day or I somehow screwed up the other virtualenv.

I am no longer seeing failed "use database blah" in the freetds logs.

[Daniel Halperin]

Confirmed that the database string is properly passed through to pymssql and that it works with pymyssql 2.1.1 (git) which is not yet released to pypi.

[Mike Bayer]

cool thanks!