MSSQL connection url format?

[Scott Jaderholm]

Hi Alchemists,

I am starting a project in Pylons that will need to access a MSSQL
database. I plan to develop on Windows and deploy on Linux. It sounds
like pyodbc is the best option and that it is reasonably well
supported. I've done a lot of Google searching but I haven't been able
to find answers to the following issues.

1. What is the correct format for the connection url?
My guess was the following:
engine = create_engine('mssql://username:password@hostname:port/database',
echo=True, module=pyodbc)
but engine.connect() fails.

Should the hostname be the IP of the server or SQL Server's
host\instance combination?

Isn't the port normally 1433?

2. I saw an archived email that said there was better support for
MSSQL in 0.3 than in 0.4. Is that still the case?

3. I'll be piggy backing on an existing ERP system and I'm trying to
decide what would be the best way to store new tables
- in the DB used by the ERP system
- in a new DB on the same MSSQL server
- in a SQLite DB

How easy does SA make it to use data from multiple DBs?

Thank you very much for your help and work on SQLAlchemy,
Scott

[sdo...@sistechnology.com]

see some notes at
http://www.sqlalchemy.org/trac/wiki/DatabaseNotes

also check dbcook.usage.sa_engine_defs.py at
(svn co)
https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/usage/
for some create/drop stuff, both pyodbs/pymssql.

> 3. I'll be piggy backing on an existing ERP system and I'm trying
> to decide what would be the best way to store new tables
> - in the DB used by the ERP system
> - in a new DB on the same MSSQL server
> - in a SQLite DB
>

sqlite db will be cheapest and fastest (in the means of rapid
develpment), but might not support all magic u want, e.g. sequences
etc. i'll say go initialy for sqlite, dont rely on specific mssql
features, and timely try things on mssql to prevent surprises. Then
once u have 70+% there, specialize if need be.

> How easy does SA make it to use data from multiple DBs?

transparent enough for an antisqler like me.
see copyall.py at
https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/misc/metadata/

have fun
svil

[Rick Morrison]

Hi Scott,

> ...to develop on Windows and deploy on Linux. It sounds

> like pyodbc is the best option

pyodbc works well in Windows, but I've heard on Linux, not so much. pymssql is your best bet for Linux. Note that pymssql does not work with unicode, limits SQL identifiers to 30 characters, and will only support up to 255 chars in VARCHAR columns. We could use some help getting pyodbc on Linux in good shape, I'm far too busy these days to assist much.

> I saw an archived email that said there was better support for
> MSSQL in 0.3 than in 0.4. Is that still the case?

yeah, sadly MSSQL is pretty lighly tested on 0.4 right now. AFAIK, it's not part of the regular test cycle.

> I'll be piggy backing on an existing ERP system and I'm trying to
> decide what would be the best way to store new tables

Well if you want to join against those other tables, and I'm guessing you do, you'd be far better off using the same DB engine as the current system. Doesn't have to be the same database, MSSQL works fine in cross-database joins.

SQLite is a great little database engine, but beware trying to make it scale up for highly concurrent situations. It's fast as hell as long as there are only readers, or only writers, but mix the two and it will fall over pretty fast. We use it here very successfully as a "static" database repository, for geocode caches, user settings, archive data, that kind of stuff. If you're going to have lots of simultaneous users and hitting the tables hard, I would go with something designed for concurrent access. 

HTH,
Rick

[Scott Jaderholm]

On 9/17/07, sdo...@sistechnology.com <sdo...@sistechnology.com> wrote:
>
> see some notes at
> http://www.sqlalchemy.org/trac/wiki/DatabaseNotes

Thank you svil for the link and helpful reply.

I'm afraid though that I still don't understand how to correctly setup
the connection. Slow learner I guess.

DatabaseNotes gives the following example:
db = create_engine('mssql://user:pass@db', module=pymssql)

but that doesn't help me much since it's not clear what goes in the place of db.

Am I supposed to create a system/file DSN under Windows with the ODBC
Data Source Administrator? If username and password are in the DSN do
they need to be in the url? Is hostname going to be host\instance?

Sorry if the answers to these questions are obvious.

Thanks,
Scott

[Gary Doades]

The url should be of the form:

mssql://user:password@server/database

Where server is the name or IP address of your SQL Server and database
is the name of the database on that server.

Hope that helps.

Cheers,
Gary.

[che]

Hi,

i checked pyodbc on linux and mssql several weeks ago.
i succeeded to connect after i create a DSN and checked that the
connection worked with DataManager (comes from unixodbc-bin package on
Ubuntu). Another thing that you must check is on windows side to open
the port of mssql to pass trough firewall. If you cant connect with
DataManager try simple

~$ telnet ip_address_of_mssql_server port

command. when you are manage to connect by DataManager then check
sqlalchemy connection - i succeeded with:

import pyodbc
db_mssql = create_engine( 'mssql://username:password@name_of_the_DSN/?
use_scope_identity=1', module= pyodbc)

> I'm afraid though that I still don't understand how to correctly setup
> the connection. Slow learner I guess.
>
> DatabaseNotes gives the following example:
> db = create_engine('mssql://user:pass@db', module=pymssql)
>
> but that doesn't help me much since it's not clear what goes in the place of db.
>
> Am I supposed to create a system/file DSN under Windows with the ODBC
> Data Source Administrator? If username and password are in the DSN do
> they need to be in the url?

u and p must be in the url

hope this helps
regards,
stefan

[Paul Johnston]

Hi,

>Should the hostname be the IP of the server or SQL Server's
>host\instance combination?
>
>

Both work ok for me.

>Isn't the port normally 1433?
>
>

Yup

>2. I saw an archived email that said there was better support for
>MSSQL in 0.3 than in 0.4. Is that still the case?
>
>

Support in 0.4 is pretty solid, main issue is update/delete statements
on tables that have a schema. That should get fixed pretty soon.

Paul