1. MySQL reading and writing

Time:2021-1-26

1、 Database reading (MySQL)

parameter accept effect default
sql or table_name string Read table name, or SQL statement nothing
con Database connection Database connection information nothing
index_col Int/sequence/False Set column as row name, (one column is multiple index) None
coerce_float Boolean Convert decimal type in database to float64 of Panda True

1. Connector: connect to MySQL

‘Database type + database driver name: / / user name: password @ machine address: port number / database name ‘

from sqlalchemy import create_engine
Connector = create_ engine('mysql+ pymysql://root : root password @ 127.0.0.1:3306 / library name? Charset = utf8 '))

Examples

2. Read SQL

(1)read_sql_query

Only SQL language can be used to read

pd.read_ sql_ Query ('sql language ', con = Data_ 1)

Examples

(2)read_sql_table

Unable to use SQL statement

pd.read_ sql_ Table ('datatable name ', con = connector)

Examples

(3)read_sql

At the same timeread_sql_tableread_sql_queryOperation of

pd.read_ SQL ('datatable name ', con = connector)
pd.read_ SQL ('sql language ', con = Data_ 1)

Examples

2、 MySQL write

to_sql()

Parameter name receive effect default
name string Database name nothing
con Database link Database link information nothing
If_exists fail/replace/append Fail cancels writing with the table name, replace with the chart name, and append with the table name fail
Index Boolean Pass index in as data true
Lindex_label String/sequence Refer to index name
Dtype Dict Write data type (column name key, data format values) None
to_sql