pythonPython

python连接mysql：增删改查

2020-05-01  本文已影响0人  弦好想断

第一种：用pymysql连接

安装pymysql

conda install pymysql

import pymysql
import pandas as pd
##创建数据库连接，依次是主机名，用户名，密码，要打开的数据库，端口号和编码类型
def get_conn():
    conn = pymysql.connect(
        host="****",
        user="****",
        password="****",
        db="****",
        charset="utf8",
        port=3306,
    )
    # 创建游标：
    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:返回结果，提取数据，返回的data是一个二维元组，((),())形式；
    '''
    conn, cursor= get_conn()
    cursor.execute(sql)
    res = cursor.fetchall() # 获取结果
    close_conn(conn, cursor)
    return res
#返回的是所有行在对应列的值

# connect.commit()
#如果对数据进行了增删改查的话，执行该语句，相当于把对数据库的操作提交上去，否则修改不会生效
close_conn(conn, cursor)#当然要有关闭游标和数据库连接的习惯
fetchone与fetchall的理解；举个例子:cursor是我们连接数据库的实例

fetchone()的使用:返回值是单个的元组,也就是一行记录,如果没有结果,那就会返回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()的使用:返回值是多个元组,即返回多个行记录,如果没有结果,返回的是()

cursor.execute(select * from user)
result=cursor.fetchall();此时select得到的可能是多行记录,那么我们通过fetchall得到的就是多行记录,
是一个二维元组
((username1,password1,nickname1),(username2,password2,nickname2),(username3,password3,nickname))

第二种：pandas内置的read_sql

需要安装sqlalchemy,但是可以DataFrame的形式读出来，方便多了

conda install sqlalchemy

sql = "select * from employees"
engine = create_engine('mysql+pymysql://root:###@localhost:3306/myemployees?charset=utf8')
#格式一点不能错，依次是用户名，密码（###），主机名，端口号，要连接的数据库，指定编码
df = pd.read_sql(sql,engine)
df#这读出来的是DataFrame，就很开心了

#把上面的语句封装为一个函数，实用些
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')#返回结果和上图一样
df_departments = reader_sql('select  * from departments')
df_departments#部门表

merged = pd.merge(df_employees,df_departments,on='department_id')
merged#将来这两个表进行内连接

result = merged.groupby(['department_name','job_id']).count()['employee_id'].reset_index()
result#对部门名和工种进行分组后计数，取员工号这一列的值，重置索引（返回一个DataFrame）

result.to_sql(name='newtable',con=engine,if_exists='append',index=False)
#这里的index如果为True的话也会报错，数据库中没有索引列（建表的时候没有预留索引列的位置）
#默认参数是fail，如果这张表本来存在的话，操作就会fail失败掉
#不建议这种直接建表操作，这样的表中字段类型会有变化，不符合原先数据要求
#建议在workbench中先create table并把所有字段的类型定义好，在使用to_sql进行写入

新建表设置好字段类型后，就可以用to_sql写入了

result.to_sql(name='new_table2',con=engine,if_exists='append',index=False)
#这里注意新建表的时候一定要把各个字段的类型搞准确，编码搞清楚不然没意识到出个错，很难受
#如果重复执行这行代码的话，会导致插入的数据成倍数增加，因为你传入的append参数，会在原数据上接着添加

del result['department_name']#删掉一列后再进行写入
result.to_sql(name='new_table2',con=engine,if_exists='append',index=False)
#如果建表时在NN列未勾选上的话，不会报错，会发现原本的值数量多了一倍，且新增的数据中的department_name列均为空值
#但如果建表时在NN列勾选上的话，表示该字段不允许为空值，就会报错
#还有如果写入的数据字段多了，与建表时给定的字段数量不符也会报错

将excel中分sheet写入数据库

import pymysql
import pandas as pd
from sqlalchemy import create_engine
import xlrd
file = r'C:\Users\谢谢你的爱1998\Desktop\mima-sofia(3).xlsx'
#分sheet写入数据库
# mysql 是数据库; pymysql是你用的连接数据库的库; root对应数据库用户名; root冒号后面写数据库的密码; @符合后面写数据库地址，
# 本地是localhost，3306是端口，trust是数据库名。
# 然后调用df.to_sql()函数将dataframe数据写入：

""" 打开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)

各参数意义

sql:SQL命令字符串
con：连接sql数据库的engine，一般可以用SQLalchemy或者pymysql之类的包建立
index_col: 选择某一列作为index
coerce_float:非常有用，将数字形式的字符串直接以float型读入
parse_dates:将某一列日期型字符串转换为datetime型数据，与pd.to_datetime函数功能类似。可以直接提供需要转换的列名以默认的日期形式转换，也可以用字典的格式提供列名和转换的日期格式，比如{column_name: format string}（format string："%Y:%m:%H:%M:%S"）。
columns:要选取的列。一般没啥用，因为在sql命令里面一般就指定要选择的列了
chunksize：如果提供了一个整数值，那么就会返回一个generator，每次输出的行数就是提供的值的大小。

read_sql本质上是read_sql_table、read_sql_query的统一方式。

三者都return返回DataFrame。

  1. read_sql_table

Read SQL database table into a DataFrame.

  1. read_sql_query

Read SQL query into a DataFrame.

to_sql

参见pandas.to_sql函数，主要有以下几个参数：

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会自动根据df列的dtype选择默认的数据类型输出，比如字符型会以sqlalchemy.types.TEXT类型输出，相比NVARCHAR，TEXT类型的数据所占的空间更大，所以一般会指定输出为NVARCHAR；而如果df的列的类型为np.int64时，将会导致无法识别并转换成INTEGER型，需要事先转换成int类型（用map，apply函数可以方便的转换）。
https://www.cnblogs.com/arkenstone/p/6271923.html

插入数据

#插入方法无需改动，传入一个动态变化的字典
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('执行成功',cursor.fetchall())
except:
    print("插入失败，数据回滚")
    connect.rollback()

更新数据

#更新，把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())

删除数据

#删除数据
sql = "delete from stuinfo where id >=10"
cursor.execute(sql)
connect.commit()
cursor.execute("select * from stuinfo")
print(cursor.fetchall())
上一篇下一篇

猜你喜欢

热点阅读