第一种：用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。
Read SQL database table into a DataFrame.
Read SQL query into a DataFrame.
to_sql
参见pandas.to_sql函数，主要有以下几个参数：
- name: 输出的表名
- con: 与read_sql中相同
- if_exits： 三个模式：fail，若表存在，则不输出；replace：若表存在，覆盖原来表里的数据；append：若表存在，将数据写到原表的后面。默认为fail
- index：是否将df的index单独写到一列中
- index_label:指定列作为df的index输出，此时index为True
- chunksize： 同read_sql
- dtype: 指定列的输出到数据库中的数据类型。字典形式储存：{column_name: sql_dtype}。常见的数据类型有sqlalchemy.types.INTEGER(), sqlalchemy.types.NVARCHAR(),sqlalchemy.Datetime()等，具体数据类型可以参考这里
还是以写到mysql数据库为例：
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())
网友评论