python-pymysql管理数据库

一:安装软件

1. 我们想要用Python管理MySQL的前期条件是要有pymysql这个软件包(pymysql:纯 Python 实现的 MySQL 客户端操作库),默认我们的系统里面是没有的所以需要我们自行安装这个软件包

安装pymysql软件包 :
这里我们可以直接用’pip’安装

(python3) [root@yanwj python2]# pip install pymysql
Collecting pymysql
  Downloading https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl (47kB)
    100% |████████████████████████████████| 51kB 23kB/s 
Installing collected packages: pymysql
Successfully installed pymysql-0.9.3
You are using pip version 10.0.1, however version 19.3.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.

二: pymysql的使用

1.安装数据库

这里用yum直接装光盘镜像中自带的mariadb数据库(CentOs-7.5)

[root@yanwj ~]# yum install mariadb-server mariadb
#mariadb数据库用yum安装完默认是没有密码的,这里需要添加密码
[root@yanwj ~]# mysqladmin -uroot password'1q2w3e4r.A'
#连接数据库,并且创建yanwj库
[root@yanwj ~]# mysql -uroot -p'1q2w3e4r.A'

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 
MariaDB [(none)]> CREATE DATABASE yanwj DEFAULT CHARSET 'utf8';

2.通过pymysql模块儿连接数据库

(1)
参数 ------------------解释
host-------------------数据库服务器地址
port-------------------数据库端号
user------------------ 用户名
password -----------登录密码
db=database-------默认操作的数据库
charset--------------数据库编码
等等… 还是有很多参数的,今天咱们就用到这么多
(2)
游标:
连接完数据库获取游标后之后才能进行执行
就像是文件对象，通过文件对象可以对文件读写
(3)
执行SQL语句

(4)
提交修改

(5)
两个close:
关闭数据库\关闭游标

栗子:

2.1 创建表

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='1q2w3e4r.A',
    db='yanwj',
    charset='utf8'
)
#创建游标
cur = conn.cursor()

#编写sql语句
#新建三张表
create_dep = """CREATE TABLE departments(
dep_id INT, dep_name VARCHAR(20),
PRIMARY KEY(dep_id)
)"""
create_emp = """CREATE TABLE employees(
emp_id INT, emp_name VARCHAR(20), birth_date DATE,
email VARCHAR(50), dep_id INT,
PRIMARY KEY(emp_id),
FOREIGN KEY(dep_id) REFERENCES departments(dep_id)
)"""
create_sal = """CREATE TABLE salary(
id INT, date DATE, emp_id INT, basic INT, awards INT,
PRIMARY KEY(id),
FOREIGN KEY(emp_id) REFERENCES employees(emp_id)
)"""

# 执行sql语句
cur.execute(create_dep)
cur.execute(create_emp)
cur.execute(create_sal)
# 如果是增删改操作，需要commit
conn.commit()
# 关闭
cur.close()
conn.close()

这个时候可以手动进入数据库查看三张表是否创建成功

MariaDB [(none)]> use yanwj;
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 [yanwj]> show tables;
+-----------------+
| Tables_in_yanwj |
+-----------------+
| departments     |
| employees       |
| salary          |
+-----------------+
3 rows in set (0.00 sec)

MariaDB [yanwj]> 

我们可以往刚刚创建的表里面插入数据\查询\修改\删除数据

2.2插入数据

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='1q2w3e4r.A',
    db='yanwj',
    charset='utf8'
)

cur = conn.cursor()

insert1 = 'INSERT INTO departments VALUES(%s, %s)'

hr = (1, '人事部')
ops = (2, '运维部')
dev = (3, '开发部')
qa = (4, '测试部')
sales = (5, '销售部')
market = (6, '市场部')

cur.executemany(insert1, [hr])
cur.executemany(insert1, [ops, dev, qa, sales, market])
youbiao
conn.commit()

cur.close()
conn.close()

2.3查询数据

import pymysql

conn = pymysql.connect(
    host='127.0.0youbiao.1',
    port=3306,
    user='root',
    passwd='1q2w3e4r.A',
    db='yanwj',
    charset='utf8'
)
cur = conn.cursor()
select1 = 'SELECT * FROM departments ORDER BY dep_id'
cur.execute(select1)
result1 = cur.fetchone()  # 取出一条记录
result2 = cur.fetchmany(2)   # 继续取出2条记录
result3 = cur.fetchall()  # 取出剩余全部记录
print(result1)
print('*'* 30)
print(result2)
print('*'* 30)
print(result3)

conn.commit()
cur.close()
conn.close()

2.4 修改数据

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='1q2w3e4r.A',
    db='yanwj',
    charset='utf8'
)
cur = conyoubiaon.cursor()
update1 = 'UPDATE departments SET dep_name=%s WHERE dep_name=%s'
cur.execute(update1, ('人力资源部', '人事部'))

conn.commit()
cur.close()
conn.close()

2.5 删除数据

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='1q2w3e4r.A',
    db='yanwj',
    charset='utf8'
)
cur = conn.cursor()
delete1 = 'DELETE FROM departments WHERE dep_id=%s'
cur.execute(delete1, (6,))

conn.commit()
cur.close()
conn.close()