In Python, anything can be written in 3 lines!
Confucius (probably didn't say that)
We're starting a new series of short tutorials that you can grab and quickly use in your project.
Sometimes we want to test an idea. Or do some PoC (Proof of Concept). Or write a "one-time" script that does something for us.
And then I come in, all in white. I give you a ready-made “recipe” that you can use somewhere.
And all this in three* lines of Python code!
* of course we do not count imports
What about today?
Sometimes we want to download some data and quickly save it to a file. In the meantime, we may “want” to modify it.
For this purpose, we can use pandas , known and liked in the Data Science and Machine Learning community!
And here is the code that allows you to download data from mysql using pandas and save it in CSV or JSON, or even Excel - after all, we have all the pandas machinery for that.
connection = sqlalchemy.create_engine("mysql://{}:{}@{}/{}?charset=utf8mb4".format(user, password, server, database)).connect() pd.read_sql_query("select * from table", connection).to_csv("output.csv") print("jeszcze linijka została elo")
Fill in user
, password
, server
and database
as desired, modify the SQL query you are using and that's it.
A slightly prettier version of this script might look like this.
#!/usr/bin/env python import sqlalchemy import pandas as pd def extract_table_to_csv(mysql_conn, table_name, output_path): query = f"select * from {table_name}" result_df = pd.read_sql_query(query, mysql_conn) result_df.to_csv(output_path) def get_db_connection(user, password, server, database): # connect to the database engine = sqlalchemy.create_engine( f"mysql://{user}:{password}@{server}/{database}?charset=utf8mb4" ) connection = engine.connect() return connection if __name__ == "__main__": # dopraw do smaku* user = "" pass = "" server = "" database = "" table_name = "" output_path = "" # ognia! (zapięte w context, żeby się połączenie ładnie zamknęło) with get_db_connection(user, password, server, database) as db_connection: extract_table_to_csv(db_connection, table_name, output_path)
Take and copy from this everyone. Any comments are welcome 🙂