DataFrame Storage

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.