Python pymysql模块对数据库Mysql,Mariadb的操作

最新推荐文章于 2023-07-30 22:55:40 发布

JJH的创世纪

最新推荐文章于 2023-07-30 22:55:40 发布

阅读量1.8k

点赞数

分类专栏： python 文章标签： python如何操作数据库 python插入字段

本文链接：https://blog.csdn.net/ck784101777/article/details/103258792

版权

python 专栏收录该内容

33 篇文章 6 订阅

订阅专栏

一、基础命令

1.pymysql模块

通过pip安装pymysql模块

pip是pypi（python package index）的安装命令，pypi是python语言的软件仓库

官方网站http://pypi.python.org

2.安装pymysql模块和mariadb

mariadb是mysql的设计者开发的开源数据库

[root@localhost ~]# yum -y install gcc
[root@localhost ~]# pip3 install pymysql

在安装之前，可以加载国内站点下载

[root@localhost ~]# mkdir ~/.pip
[root@localhost ~]# vim ~/.pip/pip.conf
[global]
index-url = http://pypi.douban.com/simple/
[install]
trusted-host=pypi.douban.com

3.连接数据库

创建连接是访问数据库的第一步

import pymysql
conn = pymysql.connect(
host='127.0.0.1', #连接ip
port=3306, #端口号
user='root', #数据库用户名
passwd='密码', #数据库密码
db='表名', #数据库名
charset='utf8' #设置了数据库的字符集
)

4.游标

游标（cursor）就是游动的标识

通俗的说，一条sql取出对应n条结果的句柄，游标可以指向这n个句柄的任意一个，沿着游标可以一次取出一行

curor=conn.cursr()

5.插入数据

对数据库表做修改操作（插入，修改，删除），必须要提交事务，commit

在进行插入操作前，必须先获取游标对象curor=conn.cursr()

insert1 = "INSERT INTO 表名(字段) VALUES(%s)"
cursor.execute(insert1, (值)) # execute执行insert语句
conn.commit() # 将更新提交到数据库

也可以一次提交多条记录，使用一个列表

insert1 = "INSERT INTO 表名(字段1，字段2) VALUES(%s, %s)"
data = [(1, 'abc'), (3, 'def')]
cursor.executemany(insert1, data) # executemany执行insert语句
conn.commit()

6.查询数据

可以从表中取出一条，多条或者全部记录

sql1="select * from 表名"
cur.execute(sql1)
result=cur.fetchone() #查找一条
print (result)
result=cur.fetchmany(2) #查找多条,2代表查找2条
print (result)
result=cur.fetchall() #查找所有
print (result)

7.删除数据

sql="delete from 表名 where id=%s"
result=cur.execute(sql,（1）) #使用游标变量执行execute删除字段id为1的字段
conn.commit()

8.修改数据

sql="update 表名 set 字段1=%s where 字段2=%s"
result=cur.execute(sql,（'abc',1）) #使用游标变量执行execute更新字段id为1的字段
conn.commit()

二、实验1-向表中插入数据

步骤一：PyMySQL安装

1) 安装gcc，有些软件包是C的源码

[root@localhost ~]# yum install -y gcc
已加载插件：fastestmirror, langpacks
dvd | 3.6 kB 00:00
Loading mirror speeds from cached hostfile
匹配 gcc-4.8.5-16.el7.x86_64 的软件包已经安装。正在检查更新。
无须任何处理

2)为了加速下载，可以使用国内开源镜像站点

[root@localhost ~]# mkdir ~/.pip
[root@localhost ~]# vim ~/.pip/pip.conf
[global]
index-url = http://pypi.douban.com/simple/
[install]
trusted-host=pypi.douban.com

3)安装pymysql

[root@localhost ~]# pip3 install pymysql

步骤二：安装mariadb-server

[root@localhost ~]# yum install –y mariadb-server

....

已安装：
        mariadb-server.x86_64 1:5.5.56-2.el7

作为依赖被安装：
        mariadb.x86_64 1:5.5.56-2.el7
        perl-DBD-MySQL. x86_64 0:4.023-5.el7

完毕！
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
[root@localhost ~]# mysqladmin password 123

步骤三：创建数据库

1)创建数据库

[root@localhost ~]# mysql -uroot -p123
MariaDB [(none)]> CREATE DATABASE DB1 DEFAULT CHARSET 'utf8';
Query OK, 1 row affected (0.00 sec)

2)创建部门表

部门表字段：部门ID、部门名称

MariaDB [(none)]> USE DB1;
Database changed
MariaDB [tedu]> CREATE TABLE departments(dep_id INT PRIMARY KEY, dep_name VARCHAR(20));
Query OK, 0 rows affected (0.04 sec)

3)创建员工表

员工表字段：员工编号、姓名、出生日期、部门ID、电话号码、email、引用外键id

MariaDB [tedu]> CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(20) NOT NULL, birth_date DATE, phone CHAR(11), email VARCHAR(50), dep_id INT, FOREIGN KEY(dep_id) REFERENCES departments(dep_id));
Query OK, 0 rows affected (0.05 sec)

4)创建工资表

工资表字段：auto_id、员工编号、日期、基本工资、奖金、工资总和

MariaDB [tedu]> CREATE TABLE salary(auto_id INT AUTO_INCREMENT PRIMARY KEY, date DATE, emp_id INT, basic INT, awards INT, FOREIGN KEY(emp_id) REFERENCES employees(emp_id));
Query OK, 0 rows affected (0.05 sec)

步骤四：向departments表插入数据

1)新建insert_data.py文件，编写代码如下：

[root@localhost day10]# vim insert_data.py

import pymysql
#连接数据库
conn = pymysql.connect(
host='127.0.0.1', #连接ip
port=3306, #端口号
user='root', #数据库用户名
passwd='123',     #数据库密码
db='DB1', #数据库名
charset='utf8' #设置了数据库的字符集
)
#创建游标
cursor = conn.cursor()
#向部门表departments中插入数据
insert1 = "INSERT INTO departments(dep_id, dep_name) VALUES(%s, %s)"
result = cursor.execute(insert1, (1, '人事部'))        # execute执行insert语句
#将更新提交到数据库
conn.commit()
#关闭游标
cursor.close()
#关闭数据库连接
conn.close()

2)执行insert_data.py文件：

[root@localhost day10]# python3 insert_data.py

3)登录mariadb查看结果：

MariaDB [tedu]>> select * from departments;
+--------+-----------+
| dep_id | dep_name |
+--------+-----------+
| 1 | 人事部 |
+--------+-----------+
1 row in set (0.00 sec)

4) 向部门表departments中插入数据还可以用如下方法：

#以上insert_data.py文件第3步可用如下代码替换：

insert1 = "INSERT INTO departments(dep_id, dep_name) VALUES(%s, %s)"
data = [(2, '运维部'), (3, '开发部')]
cursor.executemany(insert1, data)

mariadb查看结果如下：

MariaDB [tedu]>> select * from departments;
+--------+-----------+
| dep_id | dep_name |
+--------+-----------+
| 1 | 人事部 |
| 2 | 运维部 |
| 3 | 开发部 |
+--------+-----------+
3 rows in set (0.01 sec)

步骤五：向employees表插入数据

1)新建insert_emp.py文件，编写代码如下：

[root@localhost day10]# vim insert_emp.py

import pymysql
#连接数据库
conn = pymysql.connect(
host='127.0.0.1', #连接ip
port=3306, #端口号
user='root', #数据库用户名
passwd='123', #数据库密码
db='DB1', #数据库名
charset='utf8' #设置了数据库的字符集
)
#创建游标
cursor = conn.cursor()
#向部门表employees中插入数据
insert1 = "INSERT INTO employees(emp_id, emp_name, birth_date,phone, email, dep_id) VALUES(%s, %s, %s, %s, %s, %s)"
result = cursor.execute(insert1, (1, '王君', '2018-9-30','15678789090', 'wj@163.com', 3))
#将更新提交到数据库
conn.commit()
#关闭游标
cursor.close()
#关闭数据库连接
conn.close()

2)执行insert_emp.py文件：

[root@localhost day10]# python3 insert_emp.py

3)登录mariadb查看结果：

MariaDB [tedu]>> select * from employees;
+--------+----------+------------+-------------+------------+--------+
| emp_id | emp_name | birth_date | phone | email | dep_id |
+--------+----------+------------+-------------+------------+--------+
| 1 | 王君 | 2018-09-30 | 15678789090 | wj@163.com | 3 |
+--------+----------+------------+-------------+------------+--------+
1 row in set (0.00 sec)

4) 向部门表employees中插入数据还可以用如下方法：

#以上insert_emp.py文件第3步可用如下代码替换：

insert1 = "INSERT INTO employees (dep_id, dep_name) VALUES(%s, %s)"
data = [(2, '运维部'), (3, '开发部')]
cursor.executemany(insert1, data)

mariadb查看结果如下：

MariaDB [tedu]>> select * from departments;
+--------+----------+------------+-------------+------------+--------+
| emp_id | emp_name | birth_date | phone | email | dep_id |
+--------+----------+------------+-------------+------------+--------+
| 1 | 王君 | 2018-09-30 | 15678789090 | wj@163.com | 3 |
| 2 | 李雷 | 2018-09-30 | 15678789090 | wj@163.com | 2 |
| 3 | 张美 | 2018-09-30 | 15678789090 | zm@163.com | 1 |
+--------+----------+------------+-------------+------------+--------+
3 rows in set (0.00 sec)

步骤六：向salary表插入数据

1)新建insert_sal.py文件，编写代码如下：

[root@localhost day10]# vim insert_sal.py

import pymysql
1)连接数据库
conn = pymysql.connect(
host='127.0.0.1', #连接ip
port=3306, #端口号
user='root', #数据库用户名
passwd='123', #数据库密码
db='DB1', #数据库名
charset='utf8' #设置了数据库的字符集
)
#创建游标
cursor = conn.cursor()
#向部门表salary中插入数据
insert2 = "INSERT INTO salary(date, emp_id,basic, awards) VALUES(%s, %s, %s, %s)"
data = [('2018-9-30', 2, 1000, 2000), ('2018-9-30', 3, 3000, 6000),('2018-9-30', 1, 8000, 9000)]
cursor.executemany(insert2, data)
#将更新提交到数据库
conn.commit()
#关闭游标
cursor.close()
#关闭数据库连接
conn.close()

2)执行insert_sal.py文件：

[root@localhost day10]# python3 insert_sal.py

3)登录mariadb查看结果：

MariaDB [tedu]>> select * from salary;
+---------+------------+--------+-------+--------+
| auto_id | date | emp_id | basic | awards |
+---------+------------+--------+-------+--------+
| 1 | 2018-09-30 | 2 | 1000 | 2000 |
| 2 | 2018-09-30 | 3 | 3000 | 6000 |
| 3 | 2018-09-30 | 1 | 8000 | 9000 |
+---------+------------+--------+-------+--------+
3 rows in set (0.01 sec)

三、实验2-创建表

创建employees表
创建部门表
创建salary表
表间创建恰当的关系

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：SQLAlchemy安装

注意：sqlalchemy可以连接各种数据库

[root@serwang ~]# pip3 install sqlalchemy
Collecting sqlalchemy
Downloading http://pypi.doubanio.com/packages/aa/cc/48eec885d81f7260b07d
961b3ececfc0aa82f7d4a8f45ff997e0d3f44ba/SQLAlchemy-1.2.11.tar.gz (5.6MB)
...
...
Installing collected packages: sqlalchemy
Running setup.py install for sqlalchemy ... done
Successfully installed sqlalchemy-1.2.11
You are using pip version 9.0.1, however version 18.0 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.

步骤二：为SQLAlchemy创建数据库

MariaDB [tedu]> CREATE DATABASE tarena DEFAULT CHARSET 'utf8';

步骤三：创建部门表，创建dbconn.py文件，编写如下代码：

1) 创建连接到数据库的引擎

[root@localhost day10]# vim dbconn.py
#!/usr/bin/env python3
from sqlalchemy import create_engine
# 创建连接到数据库的引擎
engine = create_engine(
#指定数据库、用户名、密码、连接到哪台服务器、库名等信息
'mysql+pymysql://root:tedu.cn@localhost/tarena?charset=utf8',
encoding='utf8',
echo=True #终端输出
)

2)创建ORM映射，生成ORM映射所需的基类

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

3)自定义映射类，创建部门表

from sqlalchemy import Column, String, Integer
class Departments(Base): # 必须继承于Base
__tablename__ = 'departments' # 库中的表名
# 每个属性都是表中的一个字段，是类属性
dep_id = Column(Integer, primary_key=True) #Integer整数类型,primary_key主键
# String字符串类型，nullable非空约束，unique唯一性约束
dep_name = Column(String(20), nullable=False, unique=True)
def __str__(self):
return '[部门ID：%s, 部门名称：%s]' % (self.dep_id, self.dep_name)
if __name__ == '__main__':
# 在数据库中创建表，如果库中已有同名的表，将不会创建
Base.metadata.create_all(engine)

4)测试脚本执行，生成部门表

[root@localhost day10]# python3 dbconn.py #成功生成部门表

5)进入数据库查看结果

#登录数据库
[root@localhost day10]# mysql -uroot -ptedu.cn
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.56-MariaDB MariaDB Server
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#查看数据库表
MariaDB [(none)]> use tarena;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [tarena]> show tables;
+------------------+
| Tables_in_tarena |
+------------------+
| departments |
+------------------+
1 row in set (0.00 sec)
MariaDB [tarena]> show create table departments;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| departments | CREATE TABLE `departments` (
`dep_id` int(11) NOT NULL AUTO_INCREMENT,
`dep_name` varchar(20) NOT NULL,
PRIMARY KEY (`dep_id`),
UNIQUE KEY `dep_name` (`dep_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

步骤四：创建员工表，在dbconn.py文件中添加如下数据：

1)创建员工表

from sqlalchemy import ForeignKey 导入外键
class Employees(Base): # 必须继承于Base
__tablename__ = 'employees' # 库中的表名
# 每个属性都是表中的一个字段，是类属性
emp_id = Column(Integer, primary_key=True) #Integer整数类型,primary_key主键
name = Column(String(20), nullable=False) # String字符串类型，nullable非空约束
gender = Column(String(6))
phone = Column(String(11))
email = Column(String(50))
dep_id = Column(Integer, ForeignKey('departments.dep_id')) #与departments中dep_id做外键关联
def __str__(self):
return '员工：%s' % self.name

4)测试脚本执行，生成员工表

[root@localhost day10]# python3 dbconn.py #成功生成员工表

5)进入数据库查看结果

#登录数据库
[root@localhost day10]# mysql -uroot -ptedu.cn
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.56-MariaDB MariaDB Server
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#查看数据库表
MariaDB [(none)]> use tarena;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [tarena]> show tables;
+------------------+
| Tables_in_tarena |
+------------------+
| departments |
| employees |
+------------------+
2 rows in set (0.00 sec)

步骤五：创建工资表，在dbconn.py文件中添加如下数据：

1)创建工资表

from sqlalchemy import Date 导入外键
class Employees(Base): # 必须继承于Base
__tablename__ = 'employees' # 库中的表名
# 每个属性都是表中的一个字段，是类属性
emp_id = Column(Integer, primary_key=True) #Integer整数类型,primary_key主键
name = Column(String(20), nullable=False) # String字符串类型，nullable非空约束
gender = Column(String(6))
phone = Column(String(11))
email = Column(String(50))
dep_id = Column(Integer, ForeignKey('departments.dep_id')) #与departments中dep_id做外键关联
def __str__(self):
return '员工：%s' % self.name
class Salary(Base): # 必须继承于Base
__tablename__ = 'salary' # 库中的表名
auto_id = Column(Integer, primary_key=True) #Integer整数类型,primary_key主键
date = Column(Date) #导入日期
emp_id = Column(Integer, ForeignKey('employees.emp_id')) #与employees中emp_id做外键关联
basic = Column(Integer) #基本工资
awards = Column(Integer) #奖金