I'm trying to query via pd.read_sql(sql, db_connection_dw)
The connection works properly and is the following:
driver= '{Devart ODBC Driver for ASE}'
server='---'
db_environment='----'
username='----'
password='----'
port='----'
connectionString =
('DRIVER='+driver+';SERVER='+server+';PORT='+port+';UID='+username+';PWD='+
password + ';DATABASE='+db_environment)
try:
db_connection_dw = pyodbc.connect(connectionString)
except:
print('could not connect')
exit(0)
The query is the following, which is a simple one that returns the first 10 registers:
sql = """SELECT TOP 10 VariableID,
PatientID,
CONVERT(VARCHAR(10), ValueTime, 23) as ValueTime
FROM PV_VariableValues
WHERE VariableID = 15002262 and NumValue = 12 and YEAR(ValueTime) = 2019 and
MONTH(ValueTime) = 7
"""
After using pd.read_sql(sql, db_connection_dw) it returns the following Dataframe:
VariableID PatientID ValueTime
0 15002262 9811 2019-07-02
1 15002262 9811 2019-07-02
2 15002262 9811 2019-07-03
3 15002262 9811 2019-07-03
4 15002262 9811 2019-07-03
5 15002262 9811 2019-07-03
6 15002262 9811 2019-07-04
7 15002262 9811 2019-07-04
8 15002262 9811 2019-07-04
9 15002262 9922 2019-07-25
The problem comes when I make a bigger query, let's say, the TOP 1000. After running the code it returns the following error:
Error: ('HY000', '[HY000] [Devart][ODBC][Adaptive Server Enterprise]Error on data reading from the connection:\r\nSe produjo un error durante el intento de conexión ya que la parte conectada no respondió adecuadamente tras un periodo de tiempo, o bien se produjo un error en la conexión establecida ya que el host conectado no ha podido responder.\r\nSocket Error Code: 10060($274C) (0) (SQLFetch)')
An alternative that I tried is to read via chunksize argument doing the following after the declaration of the sql string:
for chunk in pd.read_sql(sql, db_connection_dw, chunksize=10):
print(chunk)
But after some prints it stops and returns the same error.
Any idea?
Thank's for advance
