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


Messages In This Thread
rows from sql query need to write to a file as columns - by sjcsvatt - Oct-08-2021, 09:09 PM

Possibly Related Threads…
Thread Author Replies Views Last Post
  What does .flush do? How can I change this to write to the file? Pedroski55 3 415 Apr-22-2024, 01:15 PM
Last Post: snippsat
  Last record in file doesn't write to newline gonksoup 3 581 Jan-22-2024, 12:56 PM
Last Post: deanhystad
  Create Choices from .ods file columns cspower 3 782 Dec-28-2023, 09:59 PM
Last Post: deanhystad
  write to csv file problem jacksfrustration 11 1,847 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,808 Nov-09-2023, 10:56 AM
Last Post: mg24
  Create csv file with 4 columns for process mining thomaskissas33 3 896 Nov-06-2023, 09:36 PM
Last Post: deanhystad
  How do I read and write a binary file in Python? blackears 6 8,089 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,238 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Read text file, modify it then write back Pavel_47 5 1,835 Feb-18-2023, 02:49 PM
Last Post: deanhystad
  Converting a json file to a dataframe with rows and columns eyavuz21 13 5,240 Jan-29-2023, 03:59 PM
Last Post: eyavuz21

Forum Jump:

User Panel Messages

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