Python 操作数据库 mysql

最新推荐文章于 2021-02-20 20:47:18 发布

myarche

最新推荐文章于 2021-02-20 20:47:18 发布

阅读量348

点赞数

分类专栏： linux MYSQL Python

本文链接：https://blog.csdn.net/myarche/article/details/104406930

版权

linux 同时被 3 个专栏收录

36 篇文章 0 订阅

订阅专栏

MYSQL

4 篇文章 0 订阅

订阅专栏

Python

2 篇文章 0 订阅

订阅专栏

开始：很多服务器默认安装的似乎 Python2 版本，我们先更新到 python3

下载安装：https://www.python.org/downloads/release/python-381/

手上有一台安装好宝塔面板环境的服务器，我们开始安装，其他服务器也相同类似

需要注意，宝塔面板核心依赖 python2 ，如果你升级 python 之后无法重启面板，请灵活切换 python 版本

mv /usr/bin/python /usr/bin/python3
mv /usr/bin/python2.7 /usr/bin/python

ssh 下下载安装：

wget https://www.python.org/ftp/python/3.8.1/Python-3.8.1.tgz

tar zxvf Python-3.8.1.tgz 

cd Python-3.8.1/

./configure

make && make install

#设置python3位默认python

rm -f /usr/bin/python
ln -s /usr/local/bin/python3 /usr/bin/python
rm -f /usr/bin/pip
ln -s /usr/local/bin/pip3.8 /usr/bin/pip

#然后执行查看 python 和 pip 版本
python -V
输出：Python 3.8.1

pip --version
输出：pip 19.2.3 from /usr/local/lib/python3.8/site-packages/pip (python 3.8)

#关键的一步，修改 yum 执行环境变量
vi /usr/bin/yum
#把/usr/bin/python 修改为/usr/bin/python2

#测试 yum 没有报错即可
yum list

#安装完成

提醒：

在 python2 中使用 mysql 库为：

import MySQLdb

在 python3 中使用 mysql 库为：

import pymysql

接下来我们以 python3 为版本，首先安装 pymysql

pip install pymysql

#输出以下内容安装成功
Collecting pymysql
  Using cached https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl
Installing collected packages: pymysql
Successfully installed pymysql-0.9.3

测试 pymysql

#进入 python 命令行导入 pymysql

python
>>>import pymysql

#如果没有报错，则一切 ok

python2 安装 MySQLdb

yum -y install epel-release 

yum -y install python-pip

使用 python 操作 mysql 流程如下：

接下来上命令行代码：

#我们在命令行下进入 python
>>> from pymysql import connect
>>> conn = connect(host="localhost",port=3306,user="mengyuanjiazheng",password="fYwpkWr",database='mengyuanjiazheng',charset="utf8")
>>> cursor = conn.cursor()
>>> cursor.execute("select * from admin;")
#输出的 1 是数据个数
1
>>> cursor.fetchone()
(1, 'admin', '824274666@qq.com', 'e10adc3949ba59abbe56e057f20f883e', datetime.datetime(2018, 12, 3, 9, 56, 23), '104.224.134.48', 1, datetime.datetime(2018, 5, 14, 16, 29, 10), datetime.datetime(2018, 12, 24, 14, 20, 45))
>>> 
#在执行一次，在取出一条，游标取法
#取出三条
>>>cursor.fetchmany(3)
#取出全部
>>>cursor.fetchall()
#关闭游标
>>> cursor.close()
#关闭连接
>>> conn.close()

然后我们详细书写查找、添加、修改、删除数据库数据库脚本，创建 start_mysql.py

#!/usr/bin/python 
from pymysql import connect

class JZ(object):
	def __init__(self):
		#构造函数
		self.conn = connect(host="localhost",port=3306,user="mengyuanjiazheng",password="Wr",database='mengyuanjiazheng',charset="utf8")
		self.cursor = self.conn.cursor()

	def __del__(self):
		#析构函数
		self.cursor.close()
		self.conn.close()

	def execute_sql(self, sql):
		self.cursor.execute(sql)
		for temp in self.cursor.fetchall():
			print(temp)

	def show_all_items(self):
		sql = "select * from goods;"
		self.execute_sql(sql)
		
	def show_cates(self):
		sql = "select * from goods_category;"
		self.execute_sql(sql)

	@staticmethod
	def print_menu():
		print('项目')
		print("1：所有项目")
		print("2：所有项目分类")
		print("3：添加一个分类")
		return input("请输入对应序号： ")
	
	def add_cate(self):
		item_name = input("请输入商品名称")
		sql = """insert into goods_category (name) values("%s")""" % item_name
		self.cursor.execute(sql)
		self.conn.commit()
	
	def run(self):
		while True:
			num = self.print_menu()

			if num == "1":
				#查询所有项目
				self.show_all_items()
			elif num == "2":
				#查询所有分类
				self.show_cates()
			elif num == "3":
				self.add_cate()
			else:
				print("输入有误，重新输入：")

def main():
	#chuang
	jz = JZ()
	jz.run()

if __name__ == "__main__":
	main()

以上并没有定义解释器，所以我们直接用 python 执行：

python start_mysql.py

小技巧：在书写命令时我们会经常碰到大小写引号不正确引起的语法错误，在 python 中可以使用如下方式来避免发生：

#使用 """ """ 包括起来，里面就可以忽略单引号和双引号的使用
sql = """insert into goods_category (name) values("%s")""" % item_name

切记增删改生效一定要使用 commit，当然也可以使用回滚

#commit 以生效

conn = conncect(****) #连接范例

#提交生效
conn.commit()

#回滚数据
conn.rollback()

关于 python 避免 sql 注入，php 有 PDO

#一个常见的注入

find_name = input("请输入要查询商品的名字")
sql = """select * from goods where name='%s';""" % fine_name
print("-------->%s<---------" % sql)

#试想一想，如果用户输入了 ' or 1=1 or '1

#查询语句就变为了
select * from goods where name='' or 1=1 or '1';
#就会将所有数据查询出来

防止注入的写法：

params = [find_name]

sql = 'select * from goods where name=%s'
count = conn.cursor.execute(sql,params)

#支持多个参数

视图，简单来说，将多表连查创建成一张虚拟的表，然后以后直接查询虚拟表，避免过长重复输入多表连查 sql 语句：

create view v_F_players as select PLAYERNO,NAME,SEX,PHONENO from PLAYERS where SEX='F' with check option;

#查询，可以带条件

select * from v_F_players where id=1;

#修改视图
alter view 视图名 as select 语句

#删除视图

drop view v_F_players;

#重命名视图

Rename table v_F_players to new_v_F_players;

操作事务，python 中默认开启事务：

表的引擎必须为 innodb

ACID：原子性、一致性、隔离性、持久性

#以下为 mysql 命令模式下进行
start transaction;
select balance from checking where customer_id = 1023;
update checking set balacne = balance - 200.00 where customerIid = 1023;
update savings set balance + 200.00 where customer_id = 1023;
commit;
rollback;

#另外一种开启事务方法，简单方法

begin;

索引：

#创建表 mysql 命令行
crate table test_index(title,vachar(10));

#python 脚本
form pymysql import

def main():
    #创建连接
    conn = connect(host="localhost",port=3306,user="mengiazheng",password="fWYwpkWr",database='mengmengiazheng',charset="utf8")
    #获取 Cursot 对象
    cursor = conn.cursor()
    #插入十万条数据
    for i in range(100000):
        cursor.execute("insert into index_test values('ha-%d')" % i)
    #提交
    conn.commit()

if __name__ == "__main__":
    main()

#以下为 mysql 命令行

select * from test_index where title='ha-9999';
#查看执行时间
show profiles;
#为表创建索引
create index title_index on test_index(title(10))
#执行查询
select * from test_index where title='ha-9999';
#再次查询时间
select * from test_index where title='ha-9999';

#对比得知，查询时间差距了两位数的级别