I am trying query python data frame from a MS SQL DB and I am getting the following error
I think error is sql syntax too long?
but i don’t know where is wrong
FUNCTION
import dash
import dash_bootstrap_components as dbc
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output
import dash_table as dt
import pandas as pd
from django_plotly_dash import DjangoDash
import pyodbc
import dash_core_components as dcc
server = '' database = '' username = '' password = '' cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server +
';DATABASE='+database+';UID='+username+';PWD=' + password) cursor = cnxn.cursor()
#sql syntax
query = """SELECT inf_billnom.year , inf_billnom.week , inf_billnom.month , Case inf_billnom.compno when '1' then '東方' when '2' then '中遠' else '物流' end , Max( inf_billnom.vslcode ) vslcode , Max( inf_billnom.vslnm ) vslnm , Max( inf_billnom.voyno ) voyno , Max( inf_billnom.vslno ) vslno , Max( inf_billnom.blno ) blno , Case inf_billnom.ix_cd when 'I' then '進口' when 'E' then '出口' else '未區分' end , inf_billnom.tax_regno , Max( inf_billnom.trade ) trade , Max( inf_billnom.tradelane ) tradelane , Max( tradelane.service ) service , Max( inf_billnom.bound ) bound , Max( inf_billnom.is_blready ) is_blready , Max( inf_billnom.cargo_nature ) cargo_nature , Max( inf_billnom.commodity + ( case when inf_billnom.commodity1 is null then ' ' else ' ' + inf_billnom.commodity1 end ) ) commodity , Max( inf_billnom.traffic_mode ) traffic_mode , Max( inf_billnom.ob_departure_date ) ob_departure_date , Max( inf_billnom.ib_arrival_date ) ib_arrival_date , Max( inf_billnom.sail_date ) sail_date , Max( inf_billnom.segment ) segment , case inf_billnom.ix_cd when 'I' then inf_billnom.consignee else inf_billnom.shipper end , Max( inf_billnom.ccp ) ccp , Max( inf_billnom.shipper_id ) shipper_id , Max( inf_billnom.shipper ) shipper , Max( inf_billnom.consignee_id ) consignee_id , Max( inf_billnom.consignee ) consignee , Max( inf_billnom.notify_id ) notify_id , Max( inf_billnom.notify ) notify , Max( inf_billnom.also_notify_id ) also_notify_id , Max( inf_billnom.also_notify ) also_notify , Max( inf_billnom.agree_num ) agree_num , Max( inf_billnom.agree_type ) agree_type , Max( inf_billnom.ctrl_reg ) ctrl_reg , Max( inf_billnom.frm_city ) frm_city , Max( inf_billnom.frm_country ) frm_country , Max( inf_billnom.to_city ) to_city , Max( inf_billnom.to_country ) to_country , Max( inf_billnom.svccode_1st ) svccode_1st , Max( inf_billnom.svccode_mother ) svccode_mother , Max( inf_billnom.pol_mother ) pol_mother , Max( inf_billnom.pod_mother ) pod_mother , Max( inf_billnom.dir ) dir , Max( inf_billnom.loadsvvd_1st ) loadsvvd_1st , Max( inf_billnom.discsvvd_last ) discsvvd_last , Max( inf_billnom.por ) por , Max( inf_billnom.fnd ) fnd , Max( inf_billnom.pol ) pol , Max( inf_billnom.pod ) pod , Max( inf_billnom.tport ) tport , Max( inf_billnom.frm_location ) frm_location , Max( inf_billnom.to_location ) to_location , Max( inf_billnom.por_region ) por_region , Max( inf_billnom.fnd_region ) fnd_region , Max( inf_billnom.terminal_1st ) terminal_1st , Max( inf_billnom.termina_last ) termina_last , Max( case when inf_billnom.nomcargos = 'Y' then '自攬貨' when inf_billnom.nomcargos = 'N' then '指定貨' else '' end ) nomcargos , Max( inf_billnom.department ) department , Max( case when employee.cname is null then inf_billnom.sales else employee.cname end ) sales , Max( inf_billnom.inside ) inside , sum( 1 ) blcnt , sum( case when 0= 0 then inf_billnom.size_20 else 0 end ) size_20 , sum( case when 0= 0 then inf_billnom.size_40 else 0 end ) size_40 , sum( case when 0= 0 then inf_billnom.size_45 else 0 end ) size_45 , sum( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 else 0 end + case when 0= 0 then inf_billnom.size_45 else 0 end ) cntrcnt , round (sum( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end ), 3) teu , Max( inf_billnom.pay_method ) pay_method , Max( inf_billnom.shipperkind ) shipperkind , Max( inf_billnom.isdg ) isdg , Max( ' ' ) emptykey , Max( ' ' ) emptykey1 , Max( ' ' ) emptykey2 , Max( inf_billnom.liner ) liner , inf_billnom.quarter , round (sum( case when inf_billnom.month = '01' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month01 , round (sum( case when inf_billnom.month = '02' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month02 , round (sum( case when inf_billnom.month = '03' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month03 , round (sum( case when inf_billnom.month = '04' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month04 , round (sum( case when inf_billnom.month = '05' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month05 , round (sum( case when inf_billnom.month = '06' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month06 , round (sum( case when inf_billnom.month = '07' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month07 , round (sum( case when inf_billnom.month = '08' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month08 , round (sum( case when inf_billnom.month = '09' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month09 , round (sum( case when inf_billnom.month = '10' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month10 , round (sum( case when inf_billnom.month = '11' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month11 , round (sum( case when inf_billnom.month = '12' then ( case when 0= 0 then inf_billnom.size_20 else 0 end + case when 0= 0 then inf_billnom.size_40 * 2 else 0 end + case when 0= 0 then inf_billnom.size_45 * 2 else 0 end) else 0 end ), 3) month12 , Max( view_fnd.value_new ) region FROM {oj inf_billnom LEFT OUTER JOIN employee ON inf_billnom.compno = employee.compno AND inf_billnom.sales
= employee.emp_id LEFT OUTER JOIN tradelane ON inf_billnom.trade = tradelane.trade AND inf_billnom.tradelane = tradelane.tradelane AND inf_billnom.compno = tradelane.compno LEFT OUTER JOIN view_fnd ON inf_billnom.compno = view_fnd.compno and ( case inf_billnom.ix_cd when 'I' then inf_billnom.fnd else inf_billnom.por end ) = view_fnd.value_old } wHERE 'TRUE' = 'TRUE' AND ( inf_billnom.compno ='1' or inf_billnom.compno = '2' or inf_billnom.compno = 'N' ) AND ( ( inf_billnom.ix_cd ='I' and inf_billnom.year + inf_billnom.month between '202001' and '202001' ) or ( inf_billnom.ix_cd ='E' and inf_billnom.year + inf_billnom.month between '202001' and '202001' ) ) Group by inf_billnom.year , inf_billnom.week , inf_billnom.month , inf_billnom.quarter , Case inf_billnom.ix_cd when 'I' then '進口' when 'E' then '出口' else '未區分' end , Case inf_billnom.compno when '1' then '東方' when '2' then '中遠' else '物流' end , inf_billnom.tax_regno , case inf_billnom.ix_cd when 'I' then inf_billnom.consignee else inf_billnom.shipper end Order by inf_billnom.year , inf_billnom.week , inf_billnom.month , inf_billnom.quarter , Case inf_billnom.ix_cd when 'I' then '進口' when 'E' then '出口' else '未區分' end , Case inf_billnom.compno when '1' then '東方' when '2' then '中遠' else '物流' end , inf_billnom.tax_regno , case inf_billnom.ix_cd when 'I' then inf_billnom.consignee else inf_billnom.shipper end;"""
df = pd.read_sql_query(query, cnxn)
ERROR
Exception in thread django-main-thread: Traceback (most recent call last): File "C:\Users\kctadmin\Envs\django3_dash\lib\site-packages\pandas\io\sql.py", line 1725, in execute
cur.execute(*args, **kwargs) pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver]語法錯誤或違反存取規則 (0) (SQLExecDirectW)')
The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "c:\users\kctadmin\appdata\local\programs\python\python37\lib\threading.py", line 926, in _bootstrap_inner
self.run() File "c:\users\kctadmin\appdata\local\programs\python\python37\lib\threading.py", line 870, in run
self._target(*self._args, **self._kwargs) File "C:\Users\kctadmin\Envs\django3_dash\lib\site-packages\django\utils\autoreload.py", line 53, in wrapper
fn(*args, **kwargs) File "C:\Users\kctadmin\Envs\django3_dash\lib\site-packages\channels\management\commands\runserver.py", line 75, in inner_run
('42000', '[42000] [Microsoft][ODBC SQL Server Driver]語法錯誤或違反存取規則 (0) (SQLExecDirectW)')
Thanks