0

I have a query containing multiple joins to tables, and it seems to execute without any errors on MSSQLServer Management Studio, but Pandas returns an error. 

"SELECT Ca.LoanAgreementID, Ca.Amount, Ca.TransactionDate, Ca.ContactID, Ca.PaymentType, " \
      "Ca.CashLedgerType, Ct.KeyValue_String, Pt.KeyValue_String, Lo.AutoNumber, Lo.IssueDateFROM CashLedger as Ca " \
      "LEFT JOIN Enum.CashLedgerTypes as Ct " \
      "ON Ca.CashLedgerType = Ct.KeyValue_Int" \
      "LEFT JOIN Enum.PaymentTypes as Pt " \
      "ON Ca.PaymentType = Pt.KeyValue_Int" \
      "LEFT JOIN LoanAgreements as Lo" \
      "ON Ca.LoanAgreementID = Lo.LoanAgreementID"

data = pd.read_sql(sql, connection)

Error is as follows: 

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT Ca.LoanAgreementID, Ca.Amount, Ca.TransactionDate, Ca.ContactID, Ca.PaymentType, Ca.CashLedgerType, Ct.KeyValue_String, Pt.KeyValue_String, Lo.AutoNumber, Lo.IssueDate FROM CashLedger as Ca LEFT JOIN Enum.CashLedgerTypes as Ct ON Ca.CashLedgerType = Ct.KeyValue_IntLEFT JOIN Enum.PaymentTypes as Pt ON Ca.PaymentType = Pt.KeyValue_IntLEFT JOIN LoanAgreements as LoON Ca.LoanAgreementID = Lo.LoanAgreementID': ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Ca'. (102) (SQLExecDirectW)")
Riley Hun
  • 2,541
  • 5
  • 31
  • 77
  • If you read your error, it indicates that the database has a problem with your SQL query, not pandas: `"[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Ca'. (102) (SQLExecDirectW)"`. This is probably due to all the line ending continuations you're using when defining your query as a string. This can lead to all sorts of problems and should be avoided. I often read in sql queries from external files, which is more reliable. If you want to write long strings in your code, see this answer for alternatives: http://stackoverflow.com/a/2059025/3339965 – root Feb 07 '17 at 00:00
  • Why not use triple quotes and space the query out normally? This will help you find the syntax error. Make sure there's a space in front of the FROM. – Andrew L Feb 07 '17 at 01:11
  • The `ON` lines in your query don't end with spaces, so you get e.g. `KeyValue_IntLEFT JOIN`, which is an error. – cco Feb 07 '17 at 02:48
  • Thanks for all the hints. I used triple quotes and it worked. – Riley Hun Feb 07 '17 at 16:11

0 Answers0