CODEWITHSUNDEEP
python-3.xsybasepymssql
Vroni
Vroni

Reputation: 417

Connect to Sybase data base using Python 3.6 and pymssql

I am trying to connect to a Sybase database and retrieve data from it. I am working on a Ubuntu 18.04 system with the Anaconda repository installed and would like to use Python 3.6.

I found a way to retrieve data from the DB with the python-sybase package, but this has dependencies on python 2.7 and is kind of outdated as far as I now.

import Sybase

db = Sybase.connect(dsn = server:port, user = usr, passwd = pwd, database = db)
c = db.cursor()
c.execute("select var1,var2,var3 from xxx where datum=1yymmdd and statnr=stat1")
list1 = c.fetchall()
print list1

The Output of this script is something like this:

[(10.8, 100, 0), (11.2, 100, 5), (11.3, 100, 10), ..., ..., ...]

I tried to use te pymssql package instead, which is compatible to python 3.x.

import pymssql
conn = pymssql.connect(server=serv:port,user=usr,password=pwd,database=db)
print(conn)
cursor = conn.cursor()
cursor.execute("select var1,var2,var3 from xxx where datum=1yymmdd and statnr=stat1")
list2 = cursor.fetchall()
print(list2)

But get the following error message, already after trying to connect to the Database, as it doesn't do print(conn): 

Traceback (most recent call last):
File "src/pymssql.pyx", line 636, in pymssql.connect
File "src/_mssql.pyx", line 1957, in _mssql.connect
File "src/_mssql.pyx", line 707, in _mssql.MSSQLConnection.__init__
_mssql.MSSQLDriverException: Could not set connection properties

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/home/progs/Test_SYBASE.py", line 9, in <module>
conn =  pymssql.connect(server=serv,user=usr,password=pwd,database=db,port=prt)
File "src/pymssql.pyx", line 645, in pymssql.connect
pymssql.InterfaceError: Could not set connection properties

So my problem is actually concerning the connection itself.

I read about the error message in the pymssql manual but don't know how to handle this.

exception _mssql.MSSQLDriverException MSSQLDriverException is raised whenever there is a problem within _mssql – e.g. insufficient memory for data structures, and so on.

exception pymssql.InterfaceError
Raised for errors that are related to the database interface rather than the database itself. A subclass of Error.

Do you have any suggestion how to handle this?

Thanks for help!

Upvotes: 4

Views: 14086

Answers (1)

Vroni
Vroni

Reputation: 417

Found a solution to my problem: As suggested by @GordThompson I used pyodbc and FreeTDS to connect. I installed the FreeTDS driver via

sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
sudo dpkg-reconfigure tdsodbc

as suggested here: https://gist.github.com/rduplain/1293636

My code to connect looks like that: 

import pyodbc
serv = server
usr = user 
passwd = password
db = database
prt = port
driver="FreeTDS"

conn = pyodbc.connect(driver=driver, server=serv, database=db,port = prt,
                  uid=usr, pwd=passwd)
print(conn)
cursor = conn.cursor()
cursor.execute("select var1,var2,var3 from xxx where datum=1yymmdd and statnr=stat1")
row = cursor.fetchall()
print(row)

EDIT: using conn_properties = '' in my pymssql.connect() call works as well, just as @GordThompson suggested.

import pymssql
conn =pymssql.connect(server=serv:port,user=usr,password=pwd,database=db, conn_properties='')
print(conn)
cursor = conn.cursor()
cursor.execute("select var1,var2,var3 from xxx where datum=1yymmdd and statnr=stat1")
list2 = cursor.fetchall()
print(list2)

Thanks for your help!

Upvotes: 5

Related Questions