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
  Last record in file doesn't write to newline gonksoup 3 364 Jan-22-2024, 12:56 PM
Last Post: deanhystad
  Create Choices from .ods file columns cspower 3 519 Dec-28-2023, 09:59 PM
Last Post: deanhystad
  write to csv file problem jacksfrustration 11 1,372 Nov-09-2023, 01:56 PM
Last Post: deanhystad
  python Read each xlsx file and write it into csv with pipe delimiter mg24 4 1,308 Nov-09-2023, 10:56 AM
Last Post: mg24
  Create csv file with 4 columns for process mining thomaskissas33 3 693 Nov-06-2023, 09:36 PM
Last Post: deanhystad
  How do I read and write a binary file in Python? blackears 6 6,014 Jun-06-2023, 06:37 PM
Last Post: rajeshgk
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,046 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Read text file, modify it then write back Pavel_47 5 1,500 Feb-18-2023, 02:49 PM
Last Post: deanhystad
  Converting a json file to a dataframe with rows and columns eyavuz21 13 4,168 Jan-29-2023, 03:59 PM
Last Post: eyavuz21
  how to read txt file, and write into excel with multiply sheet jacklee26 14 9,515 Jan-21-2023, 06:57 AM
Last Post: jacklee26

Forum Jump:

User Panel Messages

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