Dynamically output CSV files

Question

I have created a SQLite database utilizing the 'Spyder' Python IDE. The database contains a table called "Engine_Data."

Each week, I have to parse the data in "Engine_Data" by the variable "Engine Code" and output the results to CSV. This unfortunately has brought me to the point where I have manually written fifteen separate SQL queries in order to generate fifteen separate CSV files, as there are fifteen different types of engine code:

Here are my queries:

q1 = pd.read_sql_query("Select * from Engine_Data WHERE Engine_Data.[Engine Code] = 'A';", conn)

q1.to_csv(Engine_Code_A_Path, index=False)

Second query:

q2 = pd.read_sql_query("Select * from Engine_Data WHERE Engine_Data.[Engine Code] = 'B';", conn)

q2.to_csv(Engine_Code_B_Path, index=False)

And so on so forth until Engine Code O!

Of course, this method is way too tiresome, so I've been attempting to generate the fifteen CSV files dynamically using an array of all the engine codes and a for loop:

Engine_Code_Array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O']

for i in Engine_Code_Array:
    q = pd.read_sql_query("Select * from Engine_Data WHERE Engine_Data.[Engine Code] = array[i];", conn)
    q.to_csv(Path, index = True)

Needless to say, I have had no luck. Any potential insight into how I can make this process totally dynamic?

Possible duplicate of SQLite parameter substitution problem

buran
– buran

2018-12-14 20:41:21 +00:00
Commented Dec 14, 2018 at 20:41 — buran
– buran, Commented Dec 14, 2018 at 20:41

mad_ · Accepted Answer · 2018-12-14 20:38:21Z

2

Parameter should be outside of quoutes

"Select * from Engine_Data WHERE Engine_Data.[Engine Code] = %s",(Engine_Code_Array[i])

answered Dec 14, 2018 at 20:38

mad_

8,2832 gold badges32 silver badges46 bronze badges

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

Comments

martineau · Accepted Answer · 2018-12-17 20:42:37Z

1

I think @mad_ has basically the right idea, but would suggest implementing it along these lines:

import os

def generate_engine_code_CSV_files(dest_path, engine_codes):
    """ Generate separate CSV file for each engine code.
    """
    for engine_code in engine_codes:
        query = (
            "Select * from Engine_Data "
                "WHERE Engine_Data.[Engine Code] = '{}';".format(engine_code))

        query = pd.read_sql_query(query)
        report_path = os.path.join(
            dest_path, 'Engine Code {} Report.csv'.format(engine_code))
        query.to_csv(report_path, index=True)

report_folder_path = "path/to/reports/folder"
generate_engine_code_CSV_files(report_folder_path, 'ABCDEFGHIJKLMNO')

edited Dec 17, 2018 at 20:42

answered Dec 14, 2018 at 22:15

martineau

124k29 gold badges181 silver badges319 bronze badges

3 Comments

Tabla841 Over a year ago

Hi martineau, you're code is helping immensely! I'm almost there. As of now the procedure is only outputting the CSV for the very last engine code (engine code O) and is not generating the other 14 CSV files. Any potential insight as to why this could be happening?

martineau Over a year ago

@Aseem: Yes, as a matter of fact I do (have an idea what's wrong). The function is probably generating all the csv files, but query.to_csv() writes each one to the exact same dest_path, so afterwards it looks like it only did the last one. See updated answer.

Tabla841 Over a year ago

Perfect! That fixed the issue completely.

Collectives™ on Stack Overflow

Dynamically output CSV files

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related