pythonsqlalchemypyodbc

PyOdbc fails to connect to a sql server instance


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'"

enter image description here

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

enter image description here

Thanks


Solution

  • 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