Running Sql query stored in CSV file

[purnima1]

Hi All,
I am developing one piece of code where I need to query oracle database and save result of query .

Query will be stored in CSV format at some local place . Python code should read query from local place and execute this.

How can I do this ? Can somebody help on this .

I donot want to hard code sql query inside python script because as per requirement we need to change the where clause again and again. I want to keep it in separate place and want to call from Python.


Can Somebody give me hint on this ??

[buran]

We are glad to help, but we are not going to do it for you. You need to read about working with files. Here is short tutorial on the topic
https://python-forum.io/Thread-Basic-Files

[purnima1]

Thanks that was helpful but I have used csv module to import query from csv file

import csv

with open('path/learning/python/query/work_phone.csv', newline='\r\n') as myFile:  
    reader = csv.reader(myFile)
    for row in reader:
        print(row)

[buran]

well, I'm not sure that would work, as csv reader turn each line into a list splitting at commas (default separator). Also reading line by line will not yield SQL statements that you can execute. You need to read the whole content of the file at once, in a single string.
Not to mention that it's strange to have SQL query in a file with csv extension and not in .sql file...

[purnima1]

When I have executed script it is giving me following output
C:\Users\pubhatia\Documents\learning\python>py import_csv.py
[' select WORK_PHONE', 'source_sys_cd', "source_sys_id from mdm_people_standard where source_sys_cd ='PWS' AND WORK_PHONE IS NOT NULL; "]

Now I got your point . Thanks for explaining We cannot use this.I was wrong .

Thanks for correcting it .
There is one more thing I was reading sql lite 3 and cx_oracle ,both can be used to run queries .But I am not getting which one would be more efficient as I have 2 million to read

[buran]

you can use it and I explained you how you can do it - simply read the whole file at once in a single string.

as to your other question - which DB do you use? sqlite is module to work with sqlite database/files, cx_oracle would work with Oracle DB...

[purnima1]

I am using oracle Database. and My code is returning list which is not good idea.

Ok I will follow as you have explained

[buran]

I am using oracle Database. and My code is returning list which is not good idea.

Ok I will follow as you have explained

In this case you need to use package/interface to work with Oracle DB, not sqlite

---

Assuming
my_query.sql

Output:
SELECT WORK_PHONE, source_sys_cd, source_sys_id
FROM mdm_people_standard
WHERE source_sys_cd ='PWS' AND WORK_PHONE IS NOT NULL;

and python code to read it

with open ('my_query.sql', 'r') as query_file:
    sql_query = query_file.read()
print(sql_query)

[purnima1]

Hi all,

I have written simple query just for testing purpose in test.txt and tried to stored it local machine.
1)select * from dual;
2)'select * from dual'
3)"select * from dual"

when I am trying to run the code getting error

import cx_Oracle
from datetime import datetime
#reading query from file 
f = open('path/test.sql')
filer = f.read()
print(filer)
f.close()

#create connection string
conn_str = u'user/pwd@db'
#setting up connection 
conn = cx_Oracle.connect(conn_str)	
c = conn.cursor()
c.execute(filer)
start_time = datetime.now()
for row in c:
    print( row)
conn.close()
end_time = datetime.now()
print('Duration: {}'.format(end_time - start_time))

Getting error for different scenario

1)Traceback (most recent call last):
File "oracle_test.py", line 14, in <module>
c.execute(filer)
cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended

2 & 3

Traceback (most recent call last):
File "oracle_test.py", line 14, in <module>
c.execute(filer)
cx_Oracle.DatabaseError: ORA-00900: invalid SQL statement



Please confirm how should I pass string in cursor to run it properly

[buran]

First one is correct syntax and should work, 2 and 3 are not because of the enclosing single/double quotes.
Does the first one work if you run it from db command line? Also make sure that there is nothing else in the sql file you are reading from. What does the print on line 6 yield?

And post threads in the proper forum, such threads don't belong to News and Discussions