Python Forum
rows from sql query need to write to a file as columns
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
rows from sql query need to write to a file as columns
#1
I have a query that may have dynamic number of columns. I have include 2 columns for testing purpose. whenever they added fields to the table the number of columns in query may change. with present code, I am getting output like below
(,1,0,6,5,,, ,',S,t,r,e,e,t, ,L,e,v,e,l, ,4,',)
-----
(,1,1,4,5,,, ,',V,i,c,t,o,r,i,a, ,S,t,r,e,e,t,',)
------
(,1,2,2,2,,, ,',F,r,a,n,k,l,i,n, ,S,t,r,.,',)
-----
*Expected output:

1065
Street Level 4
-----
1145
Victoria Street
-------
1222
Franklin Str.
----

Quote:Python Code:
----------------

import cx_Oracle
from configparser import ConfigParser

filename='config_db.ini'
parser = ConfigParser()
parser.read(filename)
my_user=parser['oracle']['username']
my_dsn=parser['oracle']['dsn']
my_passwd=parser['oracle']['password']

print('user:',my_user)

sql_1= """
select cust_id,address addr from
(
select 1065 cust_id,'Street Level 4' address from dual union all
select 1145 cust_id,'Victoria Street' address from dual union all
select 1222 cust_id,'Franklin Str.' address from dual
)
"""

##No of column in the query would be dynamic...today 2 fields .... they may add more fields to the db table

conn = cx_Oracle.connect(my_user,my_passwd,my_dsn)
cursor = conn.cursor()
cursor.execute(sql_1)
qry_result = cursor.fetchall()
cursor.close()
conn.close()

with open('test_file.txt','w') as wf:
for i in qry_result:
print(type(i))
print('i:',i) ## string here
#f_result= ','.join(map(','.join,i))
f_result= ','.join(str(i))##converting into tuple
print(type(f_result))
print('f_result:',f_result)
wf.write(f_result+"\n")##writing to the file
wf.write('----------'+"\n")
Reply
#2
Use Code tags
The problem is this,is not converting into tuple.
>>> f_result= ','.join(str(s))
>>> f_result
'h,e,l,l,o, ,w,o,r,l,d' 
Bye your output so is input comma separated.
1065, Street Level 4
Test,i make input like over each on new line.
with open('test.txt') as f, open('test_file.txt', 'w') as wf:
    for line in f:
        line = line.strip().replace(', ', '\n')
        wf.write(f'{line}\n')
        wf.write('----------\n')
Output:
hello world ---------- 1065 Street Level 4 ---------- car 12456 ----------
Reply
#3
I will try and get back to you.
Reply
#4
I didn't understand your code, ... Where is the query out being passed to the for loop?? could you please elaborate?
Reply
#5
I tried like this:
with open('test_file.txt','w') as wf:
for i in qry_result:
f_result= ''.join(str(i))##converting into tuple
splitstring = f_result.strip().replace(', ', '\n')
wf.write(f'{splitstring}\n')
wf.write('----------'+"\n")

output:

(1065
'Street Level 4')
----------
(1145
'Victoria Street')
----------
(1222
'Franklin Str.')
----------
Reply
#6
I don't want brackets and single quotes over the text value
Reply
#7
(Oct-08-2021, 10:54 PM)sjcsvatt Wrote: I didn't understand your code, ... Where is the query out being passed to the for loop?? could you please elaborate?
test.txt:
Output:
hello, world 1065, Street Level 4 car, 12456
Quote:I don't want brackets and single quotes over the text value
Show a sample of how input qry_result look and what data type it is.
By output it look something liker this.
>>> s = ('1065', "'Street Level 4'")
>>> # Clean up
>>> s = ', '.join(s)
>>> s
"1065, 'Street Level 4'"
>>> s.replace("'", '')
'1065, Street Level 4' 
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Photo how I write the output into xml file in python? 3lnyn0 1 259 Oct-31-2021, 05:40 PM
Last Post: Gribouillis
  making variables in my columns and rows in python kronhamilton 2 269 Oct-31-2021, 10:38 AM
Last Post: snippsat
  Can't write to file Mark17 2 352 Sep-24-2021, 03:45 PM
Last Post: Mark17
  Merging spreadsheets with the same columns and extracting rows with matching entries johnbernard 3 594 Aug-19-2021, 03:08 PM
Last Post: johnbernard
  Summing up rows and columns plumberpy 3 643 Aug-18-2021, 05:46 AM
Last Post: naughtyCat
  How to write in text file - indented block Joni_Engr 3 595 Aug-09-2021, 06:30 PM
Last Post: deanhystad
  Partial Matching Rows In Pandas DataFrame Query eddywinch82 1 607 Jul-08-2021, 06:32 PM
Last Post: eddywinch82
  Read and write active Excel file euras 4 757 Jun-29-2021, 11:16 PM
Last Post: Pedroski55
Lightbulb [Solved] df.loc: write data in certain rows ju21878436312 1 544 Jun-28-2021, 06:49 AM
Last Post: ju21878436312
  [Solved] Using readlines to read data file and sum columns Laplace12 4 786 Jun-16-2021, 12:46 PM
Last Post: Laplace12

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020