CODEWITHSUNDEEP
pythonpandasdataframe
Tabla841
Tabla841

Reputation: 109

Dynamically output CSV files

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?

Upvotes: 1

Views: 537

Answers (2)

martineau
martineau

Reputation: 123423

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')

Upvotes: 1

mad_
mad_

Reputation: 8273

Parameter should be outside of quoutes

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

Upvotes: 2

Related Questions