Python visual data analysis 09, pandas_ MySQL read / write

​

 ​ edit

Python Visual data analysis 09、Pandas_MySQL Reading and writing

Preface

Blog ：【 Red eye aromatherapy blog _CSDN Blog - Computer theory ,2022 Blue Bridge Cup ,MySQL Domain Blogger 】

This article is written by 【 Red eye aromatherapy 】 original , First appeared in CSDN

2022 The greatest wish of the year ：【 Serve millions of technical people 】

Python Initial environment address ：【Python Visual data analysis 01、python Environment building 】 

Environmental requirements

Environmental Science ：win10

development tool ：PyCharm Community Edition 2021.2

database ：MySQL5.6

Catalog

Python Visual data analysis 09、Pandas_MySQL Reading and writing

Preface

Environmental requirements

Preface

Pre environment

Basic operation

MySQL Additions and deletions

MySQL Read operation

Preface

stay Python in , The most famous ORM The frame is SQLAlchemy. Use SQLAlchemy The steps to write data to the database are as follows ：
Import SQLAlchemy Modular create_engine() Functions and pandas() function
Create the engine , The format of the incoming string is ： Database type +Python Connect mysql The name of the library :// user name : password @IP Address : Port number / Database name
Use Pandas Under the io.sql Under the module of to_sql() Function will DataFrame The data in the object is saved to the database
Use Pandas Module read_sql() Function to read the records in the database , And save to DataFrame In the object

Pre environment

pip3 install sqlalchemy

​ edit

pip3 install pymysql

​ edit

Basic operation

1、 open MySQL service

​ edit

2、 establish 【mytest】 database

3、 establish 【user】 surface

​ edit

Example ：

import pandas as pdfrom sqlalchemy import create_engine # introduce create_engine Method df = pd.DataFrame({"id": [1, 2, 3], "name": [" Lei Jing ", " Xiaofeng ", " Spring dream "], "age": ["21", "22", "20"]})conn = create_engine('mysql+pymysql://root:[email protected]:3306/mytest?charset=utf8')# take df The object is saved to the database named mytest The library of , The name is user In the database table pd.io.sql.to_sql(df, 'user', conn, schema='mytest', if_exists='append')# # perform “select * from words;”SQL Statement to read data in the database df1 = pd.read_sql('select * from user;', con=conn)print(df1)

​

MySQL Additions and deletions

import pandas as pdfrom sqlalchemy import create_engine # introduce create_engine Method from sqlalchemy.orm import sessionmakerconn = create_engine('mysql+pymysql://root:[email protected]:3306/mytest?charset=utf8')# modify - Delete DB_Session = sessionmaker(bind=conn)session = DB_Session()# session.execute("insert into user values(3,0,' Little dragon female ',22)")# session.execute("update user set name=' Xiaofeng ' where id=2")session.execute("delete from user where id=4")session.commit()# # perform “select * from words;”SQL Statement to read data in the database df = pd.read_sql('select * from user;', con=conn)print(df)

MySQL Read operation

import pandas as pdfrom sqlalchemy import create_engine # introduce create_engine Method conn = create_engine('mysql+pymysql://root:[email protected]:3306/mytest?charset=utf8')# # perform “select * from words;”SQL Statement to read data in the database df = pd.read_sql('select * from user;', con=conn)print(df)# Basic information print(df.info)# View column names print(df.columns)# View the data types of each column print(df.dtypes)# View Subscripts print(df.index)# Before data browsing 2 strip print(df.head(2))# see name To age Column print(df.loc[:, "name":"age"])# Basic statistics print(" Maximum age ：", df.age.max())print(" Average age ：", df.age.mean())# Inquire about print(df[df.name == " Spring dream "])# Sort ·True positive sequence False In reverse order print(df.sort_values(by=["age"], ascending=False))# In the second column 【 The subscript is 1】 Add columns df.insert(1, "sex", " Woman ")print(df)# Add columns at the end df["introduce"] = " Woman "print(df)# Delete a line df = df.drop(1)print(df)# Replace value = pd.Series([1, " Woman ", " Thunder and quiet ", 20, " Big eyed girl "], index=["id", "sex", "name", "age", "introduce"])df.loc[0] = valuevalue = pd.Series([4, " Woman ", " Little dragon female ", 18, " Iceberg beauty "], index=["id", "sex", "name", "age", "introduce"])df.loc[3] = valueprint(df)# Number of pieces print(len(df))

   index  id name  age
0      0   1   Lei Jing   21
1      1   2   Xiaofeng   22
2      2   3   Spring dream   20
<bound method DataFrame.info of    index  id name  age
0      0   1   Lei Jing   21
1      1   2   Xiaofeng   22
2      2   3   Spring dream   20>
Index(['index', 'id', 'name', 'age'], dtype='object')
index     int64
id        int64
name     object
age       int64
dtype: object
RangeIndex(start=0, stop=3, step=1)
   index  id name  age
0      0   1   Lei Jing   21
1      1   2   Xiaofeng   22
  name  age
0   Lei Jing   21
1   Xiaofeng   22
2   Spring dream   20
Maximum age ： 22
Average age ： 21.0
   index  id name  age
2      2   3   Spring dream   20
   index  id name  age
1      1   2   Xiaofeng   22
0      0   1   Lei Jing   21
2      2   3   Spring dream   20
   index sex  id name  age
0      0   Woman   1   Lei Jing   21
1      1   Woman   2   Xiaofeng   22
2      2   Woman   3   Spring dream   20
   index sex  id name  age introduce
0      0   Woman   1   Lei Jing   21         Woman
1      1   Woman   2   Xiaofeng   22         Woman
2      2   Woman   3   Spring dream   20         Woman
   index sex  id name  age introduce
0      0   Woman   1   Lei Jing   21         Woman
2      2   Woman   3   Spring dream   20         Woman
   index sex  id name  age introduce
0    NaN   Woman   1   Thunder and quiet   20       Big eyed girl
2    2.0   Woman   3   Spring dream   20         Woman
3    NaN   Woman   4   Little dragon female   18       Iceberg beauty
3

Process finished with exit code 0

​