PyOdbc fails to connect to a sql server instance

Question

I am trying to connect to a sql server instance using pyodbc version 3.0.6., SQLAlchemy 1.0.4 on Windows 7 using a Python 2.7 (32 bit). I am using a connection string as follows

    DRIVER={SQL Server};SERVER=mymachinename;DATABASE=mydb;UID=sa;PWD=admin1;

but I keep getting this error

Could not parse rfc1738 URL from string 'DRIVER={SQL Server};SERVER=mymachinename;DATABASE=mydb;UID=sa;PWD=admin1'

I am using the connection string with sqlSoup.Something like this

    db = sqlsoup.SQLSoup(connectionstring)

Edit I tried using Automap from SqlAlchemy and it also fails with the same message

Traceback (most recent call last): File "C:\Sandbox\Python scripts\BumpValues\newConnectivityTest.py", line 7, in engine = create_engine("DRIVER={SQL Server};SERVER=gagan;DATABASE=persons;UID=sa;PWD=admin1;") File "C:\Python27\lib\site-packages\sqlalchemy\engine__init__.py", line 386, in create_engine return strategy.create(*args, **kwargs) File "C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 49, in create u = url.make_url(name_or_url) File "C:\Python27\lib\site-packages\sqlalchemy\engine\url.py", line 176, in make_url return _parse_rfc1738_args(name_or_url) File "C:\Python27\lib\site-packages\sqlalchemy\engine\url.py", line 225, in _parse_rfc1738_args "Could not parse rfc1738 URL from string '%s'" % name) sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'DRIVER={SQL Server};SERVER=gagan;DATABASE=persons;UID=sa;PWD=admin1;' [Finished in 0.4s with exit code 1] [shell_cmd: python -u "C:\Sandbox\Python scripts\BumpValues\newConnectivityTest.py"]

and my code is as follows

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine


Base = automap_base()
engine = create_engine("DRIVER={SQL Server};SERVER=gagan;DATABASE=persons;UID=sa;PWD=admin1;")
Base.prepare(engine, reflect=True)

usrs = Base.classes.users
print(usrs)

Can you please let me know what am I doing wrong here ?

Edit I would suggest this is not a duplicate of the question that Bryan has suggested is a duplicate of . For example , I am following the same example as was suggested in the question

    import sqlalchemy as sa
engine = sa.create_engine('mssql+pyodbc://sa:admin1@gagan/persons')

but even it fails with the same message

Here are the snapshots .. the first one uses the connection string of type "'mssql+pyodbc://user:password@server/database'"

while the second one uses the connection string of type 'DRIVER={SQL Server};SERVER=gagan;DATABASE=people;UID=sa;PWD=admin1;

Thanks

Answer 1

OK, this seems to have resolved the issue

import urllib    
connection_string = "DRIVER={SQL Server};Database=people;SERVER=gagan;UID=sa;PWD=admin1"
connection_string = urllib.parse.quote_plus(connection_string) 
connection_string = "mssql+pyodbc:///?odbc_connect=%s" % connection_string

Answer 2

There are other drivers available in Windows ecosystem, you can try the other 2 as well :)

There are actually two or three SQL Server drivers written and distrubuted by Microsoft: one referred to as "SQL Server" and the other as "SQL Native Client" and "SQL Server Native Client 10.0}".

DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password

DRIVER={SQL Native Client};SERVER=dagger;DATABASE=test;UID=user;PWD=password

DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password

Reference: https://code.google.com/p/pyodbc/wiki/ConnectionStrings

EDIT: 1

Since SQLSoup which is written on top of SQLAlchemy you will have to use the following connection string:

"mssql+pyodbc://sa:admin1@mymachinename/mydb"

Reference: http://docs.sqlalchemy.org/en/rel_0_8/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc