Python和MySQL数据库操作

因为最近稍稍用上了点数据库，所以记录一下防止忘了，也可以给大家看看防止进坑

安装和卸载

具体安装步骤请参考本文章，写的很详细
https://www.jb51.net/article/99626.htm

安装网址：
https://www.mysql.com/

在此页面下安装
推荐下面这种下载方式注意安装的时候选择custom的自定义安装，不然会默认安装在系统盘导致系统变慢，这谁也不想看到的

当然，安装错了位置也有方法解决，重新点击installer安装包进行卸载就行了，注意是全部卸载连同installer

网上的那些删除注册表啥的我也不清楚，反正我起先装到C盘去了，现在卸载后C盘也正常

2117686.png)

输入
show databases;
进行实验

如图所示即代表你安装成功啦！

最后需要配置好环境变量即可使用

MySQL有专门的的可视化界面，下面就可以对数据进行操作啦
下图是workbench可视化界面

这里的new_schema代表数据库，table子目录下的是数据表

导入的包

import pandas as pd 
import pymysql
from sqlalchemy import create_engine

数据库测试

通过下面的语句可以判断是否合数据库进行了连接

import pymysql

# 打开数据库连接

db = pymysql.connect("localhost","root","密码", '数据库')
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)

如果报错大概有那么几种情况：
1.你把数据库进程关了
2.环境变量就没配置好
3.密码错了
4.数据库名字不对

正常输出入下图

数据库插入

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

上面的是单条语句的插入

import pymysql

a = [('admin1', 'admin1@qq.com', 1), ('admin2', 'admin2@qq.com', 1), ('admin3', 'admin3@qq.com', 1), ('admin4', 'admin4@qq.com', 1), ('admin5', 'admin5@qq.com', 1)]


db = pymysql.connect(host='localhost', user='root', password='密码', port=3306, db='数据库名字')
cursor = db.cursor()
'''
#批量创建测试账号
usersvalues=[]
for i in range(1,5):
  usersvalues.append(('参数值1'+str(i),'参数值2', i))
#批量插入数据
cursor.executemany('insert into students(id, name, age) value(%s,%s,%s)', usersvalues)
db.commit()
'''
sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'
try:
    #cursor.executemany(sql, a)
    db.commit()
except:
    db.rollback()

db.close()

上面是多条语句的插入

数据库读取

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","root","密码", '数据库名称')
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

#这个是以元组的形式出现
'''  
# SQL 查询语句
sql = "SELECT * FROM 数据表名称" 
# 执行SQL语句
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
print(results)

效果图：

但上面的是以元组形式出现，所以可以用列表存储再输出

如下图代码所示

try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      company = row[5]
      exp = row[6]
       # 打印结果
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s,company=%s,exp=%s" % \
             (fname, lname, age, sex, income ,company, exp))
except:
   print ("Error: unable to fetch data")

效果图：

数据库结合pandas

数据库批量插入

因为有时候需要插入大量的数据，用insert语句会很慢

同时：（下面很关键）

网上有人说用pymysql的to_sql可以实现dataframe数据的导入，其实这没错，但是速度会让你怀疑人生，所以下面有另外一个方法

import pandas as pd 
import pymysql
from sqlalchemy import create_engine


data = pd.read_csv('拉勾网多线程.csv')
#随机抽取数据
data1 = data.iloc[0:5]
data2 = data.iloc[6:11]

engine = create_engine("mysql+pymysql://{}:{}@{}/{}?charset={}".format('root', '密码', '127.0.0.1:3306', 'new_schema','utf8mb4'))
con = engine.connect()#创建连接

#可多次插入，但是会重复数据
data1.to_sql(name='csv_test5', con=con, if_exists='append', index=False)
data2.to_sql(name='csv_test5', con=con, if_exists='append', index=False)

这样就可以实现数据的批量插入而且速度还不慢！但是目前没想到重复数据如何过滤掉

这里的编码要注意一下，‘‘utf8mb4’’代表要插入中文数据。同时engine = create_engine("mysql+pymysql这一句里面的pymysql如果你用的是mysqldb需要替换掉

上图是数据库批量插入的效果

数据库批量读取

同时读取也可以采用类似方法

import pandas as pd 
import pymysql
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://{}:{}@{}/{}?charset={}".format('root', '密码', '127.0.0.1:3306', 'new_schema','utf8mb4'))
con = engine.connect()#创建连接

# 查询语句，选出employee表中的所有数据
sql = ''' select * from csv_test5; '''
# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
# 输出employee表的查询结果
print(df)

读取之后就是dataframe格式

但是目前有一丢丢疑问
会有warning：:\Python\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, “Incorrect string value: ‘\xD6\xD0\xB9\xFA\xB1\xEA…’ for column ‘VARIABLE_VALUE’ at row 1”)
result = self._query(query)
然而我也不知道这是什么情况，网上说是编码问题，但是改了没用，但是这个对数据的写入和读取并没有影响所以就没有管

数据导出

注意：

select * from sell
INTO OUTFILE 'D:/MySQL/MySQL Server 8.0/Uploads/a.txt';

导出格式必须是’/‘而不是’'和Python的有些不一样要注意
但是这里个单引号改成双引号之后’ / ‘和’ \ ’ 又行了。。。

同时
出现错误：ERROR 1290（HYO00）：The MySQL server is running with the–secure-file-pri v option so it cannot execute this statement的原因解决：

输入：

show global variables like '%secure_file_priv%';

找到：导入文件所在的安全目录。

#导出为sql格式`
mysqldump -u root -p selldb emp > C:\Users\ThinkPad\Desktop\作业\dump.sql

数据导入

导入数据报错：
ERROR 3948(42000):Loading local data is disabled;this must be enabled on both the client and server sides

原因：
权限不够

解决：
依次输入：

SHOW VARIABLES like 'local_infile';
#设置成ON有一定风险，不用的时候依旧改为OFF
SET GLOBAL local_infile=ON;
#这个登录要在cmd里面使用，workbench和MySQL的Unicode页面都无法正常导入
mysql --local-infile=1 -u root -p
LOAD DATA LOCAL INFILE 'D:/MySQL/MySQL Server 8.0/Uploads/a.txt' INTO TABLE sell;

#导入sql格式数据
mysql -u root -p 回车 输入密码
mysql> use 爬虫测试
mysql>source C:\Users\ThinkPad\Desktop\作业\dump.sql
#在Linux下格式有所不同
mysql>source /home/user/data/fileName.sql
（以上操作均应在cmd中运行）

旧表插入新表

CREATE TABLE score_2 select * from  score s WHERE s.Chinese=90;

binlog的介绍

主要命令

show variables like 'log_%';
#查看最新的 binlog 日志文件。
show master status
#查看所有的 binlog 日志文件。
show binary logs;
show binlog events in 'LAPTOP-8P976E0F-bin.000037';

#这里的mybase是你要恢复的数据的名称，如果你误删了数据库，那么需要注意的是最后不需要带上数据库名称
mysqlbinlog "d:\MySQL\MySQL Server 8.0\Data\LAPTOP-8P976E0F-bin.000037" --start-position 425 --stop-position 1432 | mysql -u root -p mybase

恢复的过程中出现了各种各样的错误：

1.ERROR 1049(42000):Unknown database。。。
这个肯定是你的数据库名字错了，或者多了个分号，下图这个就是这个情况

2.Errcode: 2 - No such file or directory错误信息
路径错了，一般是把双引号改成单引号，反斜杠换掉，或者添加双引号，下图的错误是因为没加双引号导致路径中的空格无法被识别，如果路径中存在空格那就必须加，没有的话可加可不加

3.unknown command
这个是我自己傻了，应该在cmd里面运行
在mysql -u root -p 后面的操作中不能带有分号，而在
里面是默认用分号结尾，原来正确的语句加了分号在里面运行就是错的如下图：
这是正确的恢复界面，打开cmd运行
正确的恢复语句

mysqlbinlog "d:\MySQL\MySQL Server 8.0\Data\LAPTOP-8P976E0F-bin.000037" --start-position 425 --stop-position 1432 | mysql -u root -p mybase

所以操作数据库最好Unicode和cmd页面一起操作的好

数据库内容备份

https://www.cnblogs.com/opsprobe/p/11616562.html

那么今天的数据库分享就到这里了，真的是心有余而力不足，希望大家都能有收获！