How to create separate csv file for each sql query in python?

Question

I have 3 sql queries in different files. I tried to run all sql queries one by one. but output of every sql query is saving in single csv file. so i want to save each query result in separate csv file.

path1 = "D:/Users/SPate233/Downloads/NS dashboard/sql_query/*.txt"
files = glob.glob(path1)
for name in files:
    try:
        with open(name) as f:
            sql_query = f.read()
            cur.execute(sql_query)
            result = cur.fetchall()

            with open("output.csv", 'w') as fp:
                a = csv.writer(fp, delimiter=',')
                a.writerow([i[0] for i in cur.description])
                a.writerows(result)

    except:
        print("error")

CSV's are written to the same file because you open them with the same open("output.csv", 'w') statement. — Ignatius
– Ignatius, Commented Mar 22, 2019 at 8:16

Kristiyan Gospodinov · Accepted Answer · 2019-03-22 08:16:36Z

1

That is happening because you overwrite the same csv every time.

path1 = "D:/Users/SPate233/Downloads/NS dashboard/sql_query/*.txt"
files = glob.glob(path1)
i = 1
for name in files:
    try:
        with open(name) as f:
            sql_query = f.read()
            cur.execute(sql_query)
            result = cur.fetchall()

            with open("output_%s.csv" % i, 'w') as fp:
                a = csv.writer(fp, delimiter=',')
                a.writerow([i[0] for i in cur.description])
                a.writerows(result)
            i+=1

    except:
        print("error")

answered Mar 22, 2019 at 8:16

Kristiyan Gospodinov

5865 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

amisha Over a year ago

thank you for the help. i need one more requirement. If i want to to generate csv file like query name e.g. - if "survey_cust.txt" query runs then csv file will be generate the name like "survey_cust.csv". how to do this, can you please help me to implement this logic?

Kristiyan Gospodinov Over a year ago

@amisha csv_name = name.split('/')[-1].split('.')[0] with open("output_%s.csv" % csv_name, 'w') as fp:

Jacob Salyers · Accepted Answer · 2019-03-22 08:26:35Z

0

You keep writing to the same CSV file. Why don't you try something like this:


path1 = "D:/Users/SPate233/Downloads/NS dashboard/sql_query/*.txt"
files = glob.glob(path1)
file_list = ["file1.csv", "file2.csv"]

for iterator, name in enumerate(files):
    try:
        with open(name) as f:
            sql_query = f.read()
            cur.execute(sql_query)
            result = cur.fetchall()

            with open(file_list[iterator], 'w') as fp:
                a = csv.writer(fp, delimiter=',')
                a.writerow([i[0] for i in cur.description])
                a.writerows(result)

    except:
        print("error")

edited Mar 22, 2019 at 8:26

answered Mar 22, 2019 at 8:18

Jacob Salyers

114 bronze badges

Collectives™ on Stack Overflow

How to create separate csv file for each sql query in python?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related