DataFrame data in Pandas can be stored either in an SQL database or directly in a CSV file.
database
1. The dataframe.to_sql() function stores DataFrame data in the database.
name: the name of the data table, string, Name of SQL table
con: link to the database, SQLAlchemy engine or DBAPI2 connection (legacy mode)
if_exists: if the SQL table already exists, {‘fail’, ‘replace’, ‘append’}, default ‘fail’
Cancel storage, fail: If table exists, do nothing.
Replace SQL table, replace: If table exists, drop it, recreate it, and insert data.
Attached to the SQL table, append: If table exists, insert data. Create if does not exist.
Index: Whether to store the row label in the SQL table, boolean, default True, Write DataFrame index as a column.
index_label: If storing a row label, specify the name of the column, string or sequence, default None, Column label for index column(s).
2. The pandas.read_sql() function reads the SQL table into the DataFrame.
Sql: SQL table to be read, string, database table name.
con: link to the database, SQLAlchemy engine
index_col: Specify the column used for the row label, string, optional, column name to use as index for the returned DataFrame object.
CSV file
1. The dataframe.to_csv() function stores DataFrame data in the specified csv file. The commonly used parameters of this function are
columns: Specify the columns to be stored, Columns to write
header: Whether to write the column names to the CSV file, boolean, default True. Write out column names. If a list of string is given it is assumed to be aliases for the column names
index: Whether to write the index into the CSV file, boolean, default True.Write row labels (index)
index_label: If index is written to a CSV file, then give the index column label (column name), string or sequence, or False, default None.
2. The pandas.read_csv() function reads the data in the CSV file into the DataFrame.
header: The row of the column name of the CSV file, int, list of ints. Row number(s) to use as the column names, and the start of the data. Defaults to 0 if no names passed, otherwise None.
index_col: Specify the column where the row label is located, int or sequence or False, default None. Column to use as the row labels of the DataFrame.
names: Column names, array-like. List of column names to use.
to sum up:
Similarities:
1. When storing, both CSV and SQL store row labels (index) by default, so you need to specify the name of the row label column, index_label.
2. When reading, both CSV and SQL need to specify which column is the row label (index) column, index_col, the difference is that CSV is specified by the column serial number, and SQL is specified by the column name.
difference:
1. SQL will always store column labels. CSV can store column labels or not.
