CODEWITHSUNDEEP
pythonsqloracle-databasecx-oracle
Cris-123
Cris-123

Reputation: 28

SQL query formatting in Python

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)

Upvotes: 0

Views: 483

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10506

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"""

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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?

Upvotes: 0

Related Questions