How to use Pymysql to connect to MySQL database

Pymysql library has always been used to connect to MySQL database for data processing, and record the use method

The installation

The installation process is very simple, just use PIP to install:

pip install pymysql
Copy the code

use

Import before using:

import pandas as pd
import pymysql
Copy the code

1. Establish connections and cursors

connection = pymysql.connect(
  host='192.168.0.0'.IP address of the server
  port=3306.Mysql default port number
  user="user".# username
  password="123456".# your password
  charset="utf8".The # character set
  db="test"  # database
)

cur = connection.cursor() Create a cursor

sql=SQL > select id,date from users

cur.execute(sql)  Execute SQL statement
Copy the code

2. Iterate over each data record to append to the list

data = []

for i in cur.fetchall():
  data.append(i)
The end result is a large list generated for each row record
Copy the code

3. Generate data frames

df = pd.DataFrame(data,columns=['id'.'date'])
df
Copy the code

This generates the final data frame to process!