一、使用pymsql庫

pymysql是Python操作MySQL數(shù)據(jù)庫的模塊。

import pymysql

使用pymysql的connect()方法建立與mysql數(shù)據(jù)庫的連接：conn=pymsql.connect()

connect方法的主要參數(shù)如下：
host：連接的mysql主機(jī)，如果本機(jī)是'localhost'；IP地址
port：連接的mysql主機(jī)的端口，默認(rèn)是3306
db：數(shù)據(jù)庫的名稱
user：連接的用戶名
password：連接的密碼
charset：通信采用的編碼方式，要求與數(shù)據(jù)庫創(chuàng)建時(shí)指定的編碼一致，否則中文會亂碼

connect方法返回的conn是一個(gè)Connection對象

Connection對象擁有如下方法：
close()關(guān)閉連接
commit()事務(wù)，提交之前的操作，使生效！
rollback()事務(wù)，放棄之前的操作
cursor()返回Cursor對象，用于執(zhí)行sql語句并獲得結(jié)果。Cursor：游標(biāo)對象
注意：pymysql操作數(shù)據(jù)庫默認(rèn)開啟事物功能

調(diào)用Connection對象的cursor()方法，返回Cursor對象，然后就可以執(zhí)行sql語句，操作數(shù)據(jù)庫。
cursor=conn.cursor() # 返回是元組形式的數(shù)據(jù)（默認(rèn)）
cursor = conn.cursor( cursor=pymysql.cursors.DictCursor ) # 指定返回字典形式的數(shù)據(jù)

Cursor對象的方法：
close()：關(guān)閉游標(biāo)
execute(operation [, parameters ])：執(zhí)行sql語句，返回受影響的行數(shù)。
##########################################
按照游標(biāo)的位置往后取值，游標(biāo)默認(rèn)位置:0
fetchone()：執(zhí)行查詢語句時(shí)，獲取查詢結(jié)果集的第一個(gè)行數(shù)據(jù)，返回一個(gè)元組。
fetchmany(n)：執(zhí)行查詢語句時(shí)，獲取查詢結(jié)果集的N行數(shù)據(jù)。
fetchall()：執(zhí)行查詢時(shí)，獲取結(jié)果集的所有行，一行構(gòu)成一個(gè)元組，再將這些元組裝入一個(gè)元組返回。
##########################################
控制游標(biāo)的位置:
scroll(value[,mode])：將游標(biāo)移動(dòng)到某個(gè)位置
mode表示移動(dòng)的方式：relative（默認(rèn)）或absolute。
mode的默認(rèn)值為relative，表示基于當(dāng)前行位置移動(dòng)到value，value為正,則向下移動(dòng)，value為負(fù),則向上移動(dòng)。
mode的值為absolute，表示基于第一條數(shù)據(jù)的位置，第一條數(shù)據(jù)的位置為0。

Cursor對象的屬性：
rowcount：只讀屬性，表示最近一次execute()執(zhí)行后受影響的行數(shù)
connection：獲得當(dāng)前連接對象

示例代碼：

import pymysql

param = {
    'host':'localhost',
    'port':3306,
    'db':'tp',
    'user':'root',
    'password':'dao0206',
    'charset':'utf8',
}
conn = pymysql.connect(**param)   #連接對象
cur = conn.cursor()   #游標(biāo)對象，采用默認(rèn)的數(shù)據(jù)格式

cur.execute("SELECT * FROM test")   #執(zhí)行sql語句，返回受影響的行數(shù)
cur.fetchall()   #獲取查詢結(jié)果

# %s:占位符
# params：增加內(nèi)容的列表或元組,多條語句可以使用嵌套
sql = "insert into test values(%s,%s)"
params = (1221,"小強(qiáng)")
cur.execute(sql, params)   #sql語句參數(shù)化，防止攻擊！

# pymysql連接數(shù)據(jù)庫默認(rèn)開啟事物，提交之前的操作，使生效！
conn.commit()

# 要及時(shí)關(guān)閉連接！
cur.close()  #關(guān)閉游標(biāo)
conn.close()  #關(guān)閉連接

注意：
雖然可以使用cur.execute執(zhí)行create table等語句
但建議在開發(fā)之初，就創(chuàng)建好數(shù)據(jù)庫表結(jié)構(gòu)，然后再將數(shù)據(jù)追加到表中。

二、使用pandas庫

pandas的DataFrame數(shù)據(jù)格式與數(shù)據(jù)庫中的表非常相似，使用pandas來操作數(shù)據(jù)庫非常方便，特別是數(shù)據(jù)較大時(shí)。
使用pandas中的read_sql和to_sql函數(shù)可以便捷的從數(shù)據(jù)庫中讀寫數(shù)據(jù)，下面以mysql數(shù)據(jù)庫為例。

1. read_sql函數(shù)：主要用于從數(shù)據(jù)庫中讀取數(shù)據(jù)，可以執(zhí)行sql語句或直接讀取整張表。

主要參數(shù)：

• sql：sql語句 或 數(shù)據(jù)庫表名
• con：數(shù)據(jù)庫連接引擎
• index_col：將某列設(shè)置為索引，默認(rèn)None
• parse_dates ：將列解析為日期格式，列表或字典
• columns：需要選取的列（當(dāng)sql參數(shù)為表名時(shí)）
• chunksize：指定每次迭代返回的塊大?。ǚ祷氐氖堑鳎?/li>

2. to_sql函數(shù)：主要用于將dataframe數(shù)據(jù)寫入數(shù)據(jù)庫。

主要參數(shù)：

• name ：數(shù)據(jù)庫的表名
• con：數(shù)據(jù)庫連接引擎
• if_exists：三種模式{“fail”,“replace”,"append"}。fail(默認(rèn))：若表存在，引發(fā)一個(gè)ValueError；replace：若表存在，覆蓋原來表內(nèi)數(shù)據(jù)；append：若表存在，將數(shù)據(jù)寫到原表數(shù)據(jù)的后面。
• index：是否將DataFrame的index單獨(dú)寫到一列中，默認(rèn)為“True”。
• index_label：當(dāng)index為True時(shí)，指定列標(biāo)簽。
• chunksize：批處理的大小，迭代寫入。
• dtype：指定列的數(shù)據(jù)類型，字典形式存儲{column_name: sql_dtype}。常見數(shù)據(jù)類型是sqlalchemy.types.INT()和sqlalchemy.types.CHAR(length=x)。注意：INT和CHAR都需要大寫，INT()不用指定長度。

3. con參數(shù)：數(shù)據(jù)庫連接引擎
python的sqlalchemy模塊提供了create_engine()函數(shù)用來初始化數(shù)據(jù)庫連接（默認(rèn)不開啟事物功能），sqlalchemy用一個(gè)字符串表示連接信息：'數(shù)據(jù)庫名+數(shù)據(jù)庫驅(qū)動(dòng)://用戶名:口令@ip地址:端口號/數(shù)據(jù)庫名?charset=utf8'

主要參數(shù)：

• 數(shù)據(jù)庫名：mysql，postgresql，oracle，sqlite等。
• 數(shù)據(jù)庫驅(qū)動(dòng)：用于連接數(shù)據(jù)庫的DBAPI的名字，mysql數(shù)據(jù)庫使用的就是pymysql，如果這一項(xiàng)不指定，將使用默認(rèn)的DBAPI。

4. 示例代碼：

import pandas as pd
from sqlalchemy import create_engine

# 數(shù)據(jù)庫連接引擎
engine = create_engine('mysql+pymysql://root:passwd@localhost:3306/database?charset=utf8', encoding='utf-8')

####### 讀 #######
sql = ''' select name age from student where gender='男'; '''    # sql查詢語句
df = pd.read_sql(sql, con = engine)    #執(zhí)行sql語句，讀取數(shù)據(jù)
print(type(df), df)   #輸出dataframe數(shù)據(jù)

####### 寫 #######
df = pd.DataFrame({'id': [1, 2, 3, 4], 'name': ['zhangsan', 'lisi', 'wangwu', 'zhuliu']})
df.to_sql('test', engine, if_exists='append')   #將dataframe數(shù)據(jù)最加到數(shù)據(jù)庫test表
print("Write to MySQL successfully!")

另外：engine.execute(sql)可以直接執(zhí)行sql語句

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:passwd@localhost:3306/database?charset=utf8', encoding='utf-8')
sql = "DROP TABLE IF EXISTS test"
engine.execute(sql)

sql = "SELECT * FROM test"
result = engine.execute(sql).fetchall() 
print(result)   #返回一個(gè)列表，列表中的每個(gè)元組都是一條記錄。

參考文獻(xiàn)：
Python操作MySQL數(shù)據(jù)庫的兩種方式pymysql和pandas
Python之使用Pandas庫實(shí)現(xiàn)MySQL數(shù)據(jù)庫的讀寫