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:XXX@190.122.12.214/XXX?charset=utf8'" 

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

Answer 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.

Answer 2

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