how to connect remote windows MSSQL server from ubuntu using sqlalchemy

Question

am trying to connect with MSSQL remotely which is in windows from ubuntu using sqlalchemy.I creted DSN like below

dbinfo.py:

 username = 'XXX'
 pw = 'XXX'
 host = '190.122.12.214'
 drivername = 'SQL Server'
 database = 'XXX'
 extra_param=''

and i mported the dbinfo.py file into db_handler.py :

import transaction
from z3c.saconfig import Session as SASession
from z3c.saconfig import EngineFactory
from zope import component
from zope.sqlalchemy import mark_changed
# sqlalchemy
import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from redindia.loginpage import dbinfo
info = {
'username' : dbinfo.username,
'pw' : dbinfo.pw,
'host' : dbinfo.host,
'drivername' : dbinfo.drivername,
'database' : dbinfo.database,
'extra_param' : ''
  }
drivername = str(info['drivername'])
username = str(info['username'])
host = str(info['host'])
database = str(info['database'])
extra_param = str(info['extra_param'])
def getDb():

  pass

def getSession(testing=False):
 try:
    return SASession()
 except component.ComponentLookupError:
    pass
# construct url to open database
  _testing_ = ''
  if testing:
     _testing_ = '_testing'
  if info['pw'] != '':
    DSN = drivername+'://'+username+':' + info['pw'] +'@'+host+'/'+database+_testing_+'?charset=utf8'+extra_param

  else:
     DSN = drivername+'://'+username+'@'+host+'/'+database+_testing_+'?charset=utf8'+extra_param

   engine_factory = EngineFactory(DSN, pool_recycle=7200)
  engine = engine_factory()

  ## create a global session
  from z3c.saconfig import GloballyScopedSession
  utility = GloballyScopedSession(bind=engine) # i think, without engine, it will find above provided one...
  from z3c.saconfig.interfaces import IScopedSession
  component.provideUtility(utility, provides=IScopedSession)
  return SASession()

session = getSession()
engine = session.get_bind()

Base = declarative_base(engine)

Base.metadata.reflect()
tables = Base.metadata.tables

and then connecting details below mentioned

def callStoreProcedure(self):
     form = self.request.form
     area = form.get('Area') 
     session = getSession()
     result = session.execute("select * from BBBB")
     result_set = result.fetchall()
     return result_set

and i configure ODBC connectivity settings

etc/odbc.ini:

  [SQL Server]
     Description=my dsn
     Driver=SQL Server
     Database=XXX
     Servername=190.122.12.214
     UID=XXX
     PWD=XXX

etc/odbcinst.ini:

   [SQL Server]
      Description = sql Driver
      Driver = /usr/local/lib/libtdsodbc.so
      Setup=/usr/local/lib/libtdsS.so
      UsageCount = 1

I configured the settings like above.But i can't able to connect MSSQL.am getting the error like below

"ArgumentError: Could not parse rfc1738 URL from string 'SQL Server://XXX:[email protected]/XXX?charset=utf8'"

Plz can anyone help me to solve this issues.Thanks in advance.

tuomur · Accepted Answer · 2013-12-18 10:30:02Z

1

The SQLAlchemy engine URL should begin with either mssql or mssql+pyodbc. See the Engine Configuration documentation.

answered Dec 18, 2013 at 10:30

tuomur

7,15838 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sankar Over a year ago

yes I got the answer to connect mssql which is in windows from ubuntu11.10 using sqlalchemy. Thanks

sankar · Accepted Answer · 2013-12-19 12:29:20Z

1

I created dbhandler.py file. It contains the details about the database connectivity.The details are below

db_handler.py:

from sqlalchemy import create_engine
def getSession(self):
   DSN="mssql://UID:PWD@IPADDRESS/DBNAME"
   return DSN

our .py file

from xxxx.yyyy.db_handler import getSession
from sqlalchemy import create_engine
def callStoreProcedure(self):
     form = self.request.form
     DSN = getSession(self)
     engine = create_engine(DSN)
     cursor = engine.execute("select * from tablename")
     result = cursor.fetchall()
     return result

Now i have connected with the database.

answered Dec 19, 2013 at 12:29

sankar

834 silver badges16 bronze badges

Collectives™ on Stack Overflow

how to connect remote windows MSSQL server from ubuntu using sqlalchemy

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related