SQL query formatting in Python

Question

I want to to query my Oracle DB, but it's not working. I think i'm missing something in formatting but unable to figure out. If i execute the same query in SQL developer, it's returning me results. But in Python it's giving 'SyntaxError: invalid syntax.' error.

import os
import cx_Oracle
import matplotlib.pyplot as plt
import numpy as np

dsn_tns = cx_Oracle.makedsn('host', '1521', service_name='S1') 
conn = cx_Oracle.connect(user=r'dev_user', password='Welcome', dsn=dsn_tns) 


reportid_count = []
count_ID =  []

c = conn.cursor()

query = 'select distinct (LTRIM(REGEXP_SUBSTR(ID, '[0-9]{3,}'), '0')) as ReportID,count(ID) from dev_user.RECORD_TABLE  group by ID'
c.execute(query) 


#loop through the rows fetched and store the records as arrays.
for row in c:
    reportid_count.append(row[0] + ',' + str(row[1]))
    count_ID.append(row[1])

for s in reportid_count:
    print(s)

Database is in localhost, error is- SyntaxError: invalid syntax — Cris-123
– Cris-123, Commented Apr 13, 2021 at 16:27
There is something wrong wrong with your quoting. The first quoted string is 'select distinct (LTRIM(REGEXP_SUBSTR(ID, ' so I expect the parser complains at everything after it, starting at [0-9]{3,}. — William Robertson
– William Robertson, Commented Apr 13, 2021 at 23:09

Christopher Jones · Accepted Answer · 2021-04-13 22:53:38Z

1

On the Python side, you need to remove embedded quotes. Try using:

query = """select distinct (LTRIM(REGEXP_SUBSTR(ID, '[0-9]{3,}'), '0')) as ReportID,count(ID) from dev_user.RECORD_TABLE  group by ID"""

answered Apr 13, 2021 at 22:53

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cris-123 Over a year ago

This has solved the problem. Thanks Chris.

Littlefoot · Accepted Answer · 2021-04-13 15:26:51Z

0

IF you're using GROUP BY, you don't have to use DISTINCT because the former already makes sure you'll get distinct result. Also, you'd probably want to group by the whole select expression (ltrim...), not just id, i.e.

SELECT ltrim(regexp_substr(id, '[0-9]{3,}'), '0') AS reportid,
       COUNT(id)
FROM dev_user.record_table
GROUP BY ltrim(regexp_substr(id, '[0-9]{3,}'), '0')

Finally, you said

in Python it's giving error.

Which error?

answered Apr 13, 2021 at 15:26

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

3 Comments

Cris-123 Over a year ago

SyntaxError: invalid syntax. This is the error i'm getting.

Cris-123 Over a year ago

Tried with the same, SyntaxError: invalid syntax.error is appearing.

Littlefoot Over a year ago

Is there, by any chance, error code? Something like ORA-01234 or PLS-01234 or similar? Because, SELECT statement itself looks OK and works properly in my Oracle 11gXE database. Perhaps you could just remove username from the FROM clause so that it looks "FROM record_table" as you're connected as DEV_USER anyway.

Collectives™ on Stack Overflow

SQL query formatting in Python

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related