第一種：用pymysql連接

安裝pymysql

conda install pymysql

import pymysql
import pandas as pd
##創(chuàng)建數(shù)據(jù)庫連接捌议，依次是主機(jī)名尘惧，用戶名隔缀，密碼眉枕，要打開的數(shù)據(jù)庫，端口號和編碼類型
def get_conn():
    conn = pymysql.connect(
        host="****",
        user="****",
        password="****",
        db="****",
        charset="utf8",
        port=3306,
    )
    # 創(chuàng)建游標(biāo)：
    cursor = conn.cursor()
    return conn, cursor
def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()
def query(sql, *args):
    '''
    :param sql:
    :param args:
    :return:返回結(jié)果，提取數(shù)據(jù)，返回的data是一個二維元組阀捅，((),())形式胀瞪；
    '''
    conn, cursor= get_conn()
    cursor.execute(sql)
    res = cursor.fetchall() # 獲取結(jié)果
    close_conn(conn, cursor)
    return res
#返回的是所有行在對應(yīng)列的值

# connect.commit()
#如果對數(shù)據(jù)進(jìn)行了增刪改查的話针余，執(zhí)行該語句饲鄙，相當(dāng)于把對數(shù)據(jù)庫的操作提交上去，否則修改不會生效
close_conn(conn, cursor)#當(dāng)然要有關(guān)閉游標(biāo)和數(shù)據(jù)庫連接的習(xí)慣

fetchone與fetchall的理解圆雁；舉個例子:cursor是我們連接數(shù)據(jù)庫的實例

fetchone()的使用:返回值是單個的元組,也就是一行記錄,如果沒有結(jié)果,那就會返回null

cursor.execute(select username,password,nickname from user where id='%s'  %(input)

result=cursor.fetchone();  此時我們可以通過result[0],result[1],result[2]得到
username,password,nickname

fetchall()的使用:返回值是多個元組,即返回多個行記錄,如果沒有結(jié)果,返回的是()

cursor.execute(select * from user)
result=cursor.fetchall();此時select得到的可能是多行記錄,那么我們通過fetchall得到的就是多行記錄,
是一個二維元組
((username1,password1,nickname1),(username2,password2,nickname2),(username3,password3,nickname))

第二種：pandas內(nèi)置的read_sql

需要安裝sqlalchemy,但是可以DataFrame的形式讀出來忍级，方便多了

conda install sqlalchemy

sql = "select * from employees"
engine = create_engine('mysql+pymysql://root:###@localhost:3306/myemployees?charset=utf8')
#格式一點不能錯，依次是用戶名伪朽，密碼（###）轴咱，主機(jī)名，端口號烈涮，要連接的數(shù)據(jù)庫朴肺，指定編碼
df = pd.read_sql(sql,engine)
df#這讀出來的是DataFrame，就很開心了

#把上面的語句封裝為一個函數(shù)坚洽，實用些
def reader_sql(query,db='myemployees'):
    sql = query
    engine = create_engine('mysql+pymysql://root:###@localhost:3306/{0}?charset=utf8'.format(db))
    df = pd.read_sql(sql,engine)
    return df
df_employees = reader_sql('select * from employees')#返回結(jié)果和上圖一樣
df_departments = reader_sql('select  * from departments')
df_departments#部門表

merged = pd.merge(df_employees,df_departments,on='department_id')
merged#將來這兩個表進(jìn)行內(nèi)連接

result = merged.groupby(['department_name','job_id']).count()['employee_id'].reset_index()
result#對部門名和工種進(jìn)行分組后計數(shù)戈稿，取員工號這一列的值，重置索引（返回一個DataFrame）

result.to_sql(name='newtable',con=engine,if_exists='append',index=False)
#這里的index如果為True的話也會報錯讶舰，數(shù)據(jù)庫中沒有索引列（建表的時候沒有預(yù)留索引列的位置）
#默認(rèn)參數(shù)是fail鞍盗，如果這張表本來存在的話，操作就會fail失敗掉
#不建議這種直接建表操作跳昼，這樣的表中字段類型會有變化般甲，不符合原先數(shù)據(jù)要求
#建議在workbench中先create table并把所有字段的類型定義好，在使用to_sql進(jìn)行寫入

新建表設(shè)置好字段類型后鹅颊，就可以用to_sql寫入了

result.to_sql(name='new_table2',con=engine,if_exists='append',index=False)
#這里注意新建表的時候一定要把各個字段的類型搞準(zhǔn)確敷存，編碼搞清楚不然沒意識到出個錯，很難受
#如果重復(fù)執(zhí)行這行代碼的話堪伍，會導(dǎo)致插入的數(shù)據(jù)成倍數(shù)增加历帚，因為你傳入的append參數(shù)，會在原數(shù)據(jù)上接著添加

del result['department_name']#刪掉一列后再進(jìn)行寫入
result.to_sql(name='new_table2',con=engine,if_exists='append',index=False)
#如果建表時在NN列未勾選上的話杠娱，不會報錯挽牢，會發(fā)現(xiàn)原本的值數(shù)量多了一倍，且新增的數(shù)據(jù)中的department_name列均為空值
#但如果建表時在NN列勾選上的話摊求，表示該字段不允許為空值禽拔，就會報錯
#還有如果寫入的數(shù)據(jù)字段多了，與建表時給定的字段數(shù)量不符也會報錯

將excel中分sheet寫入數(shù)據(jù)庫

import pymysql
import pandas as pd
from sqlalchemy import create_engine
import xlrd
file = r'C:\Users\謝謝你的愛1998\Desktop\mima-sofia(3).xlsx'
#分sheet寫入數(shù)據(jù)庫
# mysql 是數(shù)據(jù)庫; pymysql是你用的連接數(shù)據(jù)庫的庫; root對應(yīng)數(shù)據(jù)庫用戶名; root冒號后面寫數(shù)據(jù)庫的密碼; @符合后面寫數(shù)據(jù)庫地址室叉，
# 本地是localhost睹栖，3306是端口，trust是數(shù)據(jù)庫名茧痕。
# 然后調(diào)用df.to_sql()函數(shù)將dataframe數(shù)據(jù)寫入：

""" 打開excel表格"""
workbook = xlrd.open_workbook(file)
sheet_names = workbook.sheet_names()
for i in sheet_names :
    data = pd.read_excel(file,sheet_name = i,index = False,encoding='utf-8')
    data.to_sql(i,con=engine,if_exists='replace',index=False)

pd.read_sql()介紹

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

各參數(shù)意義

sql:SQL命令字符串
con：連接sql數(shù)據(jù)庫的engine野来，一般可以用SQLalchemy或者pymysql之類的包建立
index_col: 選擇某一列作為index
coerce_float:非常有用，將數(shù)字形式的字符串直接以float型讀入
parse_dates:將某一列日期型字符串轉(zhuǎn)換為datetime型數(shù)據(jù)踪旷，與pd.to_datetime函數(shù)功能類似曼氛』砘裕可以直接提供需要轉(zhuǎn)換的列名以默認(rèn)的日期形式轉(zhuǎn)換，也可以用字典的格式提供列名和轉(zhuǎn)換的日期格式舀患，比如{column_name: format string}（format string："%Y:%m:%H:%M:%S"）徽级。
columns:要選取的列。一般沒啥用聊浅，因為在sql命令里面一般就指定要選擇的列了
chunksize：如果提供了一個整數(shù)值餐抢，那么就會返回一個generator，每次輸出的行數(shù)就是提供的值的大小低匙。

read_sql本質(zhì)上是read_sql_table旷痕、read_sql_query的統(tǒng)一方式。

三者都return返回DataFrame顽冶。

read_sql_table

Read SQL database table into a DataFrame.

read_sql_query

Read SQL query into a DataFrame.

to_sql

參見pandas.to_sql函數(shù)苦蒿，主要有以下幾個參數(shù)：

name: 輸出的表名
con: 與read_sql中相同
if_exits：三個模式：fail，若表存在渗稍，則不輸出佩迟；replace：若表存在，覆蓋原來表里的數(shù)據(jù)竿屹；append：若表存在报强，將數(shù)據(jù)寫到原表的后面。默認(rèn)為fail
index：是否將df的index單獨寫到一列中
index_label:指定列作為df的index輸出拱燃，此時index為True
chunksize：同read_sql
dtype: 指定列的輸出到數(shù)據(jù)庫中的數(shù)據(jù)類型秉溉。字典形式儲存：{column_name: sql_dtype}。常見的數(shù)據(jù)類型有sqlalchemy.types.INTEGER(), sqlalchemy.types.NVARCHAR(),sqlalchemy.Datetime()等碗誉，具體數(shù)據(jù)類型可以參考這里
還是以寫到mysql數(shù)據(jù)庫為例：

df.to_sql(name='table', 
          con=con, 
          if_exists='append', 
          index=False,
          dtype={'col1':sqlalchemy.types.INTEGER(),
                 'col2':sqlalchemy.types.NVARCHAR(length=255),
                 'col_time':sqlalchemy.DateTime(),
                 'col_bool':sqlalchemy.types.Boolean
          })

注：如果不提供dtype,to_sql會自動根據(jù)df列的dtype選擇默認(rèn)的數(shù)據(jù)類型輸出召嘶，比如字符型會以sqlalchemy.types.TEXT類型輸出，相比NVARCHAR哮缺，TEXT類型的數(shù)據(jù)所占的空間更大弄跌，所以一般會指定輸出為NVARCHAR；而如果df的列的類型為np.int64時尝苇，將會導(dǎo)致無法識別并轉(zhuǎn)換成INTEGER型铛只，需要事先轉(zhuǎn)換成int類型（用map，apply函數(shù)可以方便的轉(zhuǎn)換）糠溜。
https://www.cnblogs.com/arkenstone/p/6271923.html

插入數(shù)據(jù)

#插入方法無需改動淳玩，傳入一個動態(tài)變化的字典
connect = pymysql.connect(host='',user='',db = '',password='',port=3306,charset='utf8')
cursor=connect.cursor()
data = {"id":'100','name':'Bob'}
table= 'stuinfo'
keys = ', '.join(data.keys())
values = ', '.join(['%s']*len(data))
sql = f"insert into {table}({keys}) values({values})"
try:
    cursor.execute(sql,tuple(data.values()))
    connect.commit()
    cursor.execute('select * from {table}'.format(table=table))
    print('執(zhí)行成功',cursor.fetchall())
except:
    print("插入失敗，數(shù)據(jù)回滾")
    connect.rollback()

更新數(shù)據(jù)

#更新非竿，把Bon的age更新為28
sql = "update stuinfo set id=%s where name = %s"
cursor.execute(sql,("28",'Bob'))
connect.commit()
cursor.execute('select * from stuinfo')
print("Successful:",cursor.fetchall())

刪除數(shù)據(jù)

#刪除數(shù)據(jù)
sql = "delete from stuinfo where id >=10"
cursor.execute(sql)
connect.commit()
cursor.execute("select * from stuinfo")
print(cursor.fetchall())

python連接mysql：增刪改查