connecting to SQL17 from python

[Baron]

Hi all,

I try to connect to a SQL17 DB using the following lines in Python

import sqlanydb

con = sqlanydb.connect(uid='dba', pwd='sql', servername='SRV1', host='localhost:2638' )

con.close()

I get following errors:

File "C:\\Python38\\lib\\site-packages\\sqlanydb.py", line 522, in connect

File "C:\\Python38\\lib\\site-packages\\sqlanydb.py", line 538, in init parent = Connection.cls_parent = Root("PYTHON")

self.api = load_library(os.getenv( 'SQLANY_API_DLL', None ), 'dbcapi.dll', 'libdbcapi_r.so',

raise InterfaceError("Could not load dbcapi. Tried: " + ','.join(map(str, names))) sqlanydb.InterfaceError: ('Could not load dbcapi. Tried: None,dbcapi.dll,libdbcapi_r.so,libdbcapi_r.dylib', 0)

[Vlad]

Has Google banned you? https://sqlanywhere-forum.sap.com/questions/23704/python3-cannot-import-sqlanydb

Can you simply tell us, what answer you have found, and what you have tried before you raised the question?

[Baron]

Thanks for the reply, yes Google didn't help me before raising the question:

https://github.com/sqlanywhere/sqlanydb/issues/18

https://github.community/t/issue-loading-dbcapi-while-connecting-to-sqlanydb/12886

[Baron]

I reinstalled my SQLAnywhere17, and now I have both 32/64, and I see the file dbcapi.dll is located under C:\\Program Files (x86)\\SQL Anywhere 17\\BIN32, but I still get the same error

[VolkerBarth]

Well, when your are running on Windows, links to Unix issues (like "sourcing" scripts) won't help, methinks...

Have you followed these steps, and have they proved successful?

[Baron]

Yes, I did the steps before.

import ctypes doesn't return any error.

I installed sqlanydb using pip install sqlanydb

[Baron]

@vlad does it work on your windows machine?

[former_SQLA_member1695059 · Accepted solution]

"Now it works with the help of PYODBC"..... here's how to do a DSN-less connection. Not too hard...

Assume> dbsrv17 -n demo17 "C:\\Users\\Public\\Documents\\SQL Anywhere 17\\Samples\\demo.db" -n demo

!!!Code starts....

import pyodbc

connection_string = "Driver=SQL Anywhere 17;Server=demo17;UID=dba;PWD=sql;DBN=demo"

connection_object = pyodbc.connect(connection_string)

cursor = connection_object.cursor()

sql_string = "select count(*) from Employees"

result = cursor.execute(sql_string)

counter = result.fetchone()[0]

print(counter)

connection_object.close()

!!! Code ends

HTH, Paul

BTW how do you put a code block in these answer boxes? I tried pre and code tags as per help and it previewed OK but when posted looked horrid. I then had to cut out all my enlightning comments!

[Baron · Accepted solution]

Thank you very much for the answer. It worked on my machine without DSN and even without service:

connection_string = "Driver=SQL Anywhere 17;UID=dba;PWD=sql;DBF=C:\\Users\\Public\\Documents\\SQL Anywhere 17\\Samples\\demo.db"

[jack_schueler]

From your comments, it looks like you are using a 32-bit version of Python (which seems odd in this age of 64-bit processors) but, in any case, is C:\\Program Files (x86)\\SQL Anywhere 17\\BIN32 in your PATH? And if you are really running a 64-bit Python, then you will need the dbcapi.dll that is in the bin64 folder (hence this needs to be in your path).

[Baron]

I had 64-bit version of Python, then uninstalled it and installed the 32-bit version of Python, and I get always the same problem.

Now it works with the help of PYODBC