Python - Pyodbc for SQL Anywhere 16?

Question

I am trying to connect to a DB via python using Pyodbc as per below:

import pyodbc
import pandas as pd

conn = pyodbc.connect('Driver={SQL Anywhere 16};'
                      'Server=***.**.**.***;'
                      'Database=**********;'
                      'Trusted_Connection=yes;'
                      )

Data = pd.read_sql_query("SELECT * FROM * WHERE Date='20180328'", conn)
print(Data)

but receive the error:

line 4, in conn = pyodbc.connect('Driver={SQL Anywhere 16};' pyodbc.Error: ('08001', '[08001] [Sybase][ODBC Driver][SQL Anywhere]Database server not found (-100) (SQLDriverConnect)')

I have a ODBC configuration on Windows with the driver "SQL Anywhere 16" and I can connect through Squirrel SQL using this ODBC connection - any ideas on this?

shwm19 shwm19 · Accepted Answer · 2018-08-22T02:02:06

This looks like an issue with your connection string to me. Perhaps I am wrong, but it is looking like the error you are facing is it is not finding a server, and bases on the error it looks almost as if you are only getting the Driver=SQL Anywhere 16 passed to it.

I mention that because in this question:Connecting to MS SQL Server with Windows Authentication using Python?

They detail the following for the strings on multiple lines in the answer

conn_str = (
r'Driver={SQL Server};'
r'Server=.\SQLEXPRESS;'
r'Database=myDB;'
r'Trusted_Connection=yes;'
)

cnxn = pyodbc.connect(conn_str)

Additionally, when searching for multi line strings we see this answer here:How to correctly write a raw multiline string in Python?

Which details:

You'd need a r prefix on each string literal

(r'on\e'
     r'\tw\o')
'on\\e\\tw\\o'

Otherwise the first portion is interpreted as a raw string literal, but the next line of string is not, so the '\t' is interpreted as a tab character.

So please try prefixing an r to each line of that literal string to make something more like

import pyodbc
import pandas as pd

conn = pyodbc.connect(r'Driver={SQL Anywhere 16};'
                      r'Server=***.**.**.***;'
                      r'Database=**********;'
                      r'Trusted_Connection=yes;'
                      )

Data = pd.read_sql_query("SELECT * FROM * WHERE Date='20180328'", conn)
print(Data)

And see if that fixes your problem.

EDIT: I edited to fix formatting.

Python - Pyodbc for SQL Anywhere 16?

1 Answers