PyMySQL

PyMySQL安装

使用pypi

• pypi即python package index
• 是python语言的软件仓库
• 官方站点为https://pypi.python.org
  

通过pip安装PyMySQL模块

• 安装依赖包
  [root@localhost packages]# yum install -y gcc
• 本地安装
  [root@localhost packages]# pip3 install PyMySQL-0.8.0.tar.gz
• 在线安装
  [root@localhost packages]# pip3 install pymysql

使用国内镜像站点

• 为了实现安装加速,可以配置pip安装时采用国内镜像站点

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

PyMySQL应用

连接数据库

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

conn	=	pymysql.connect(
host='127.0.0.1',	
port=3306,	
user='root',
passwd='123456',	
db='TestDB',	
charset='utf8')

游标

• 游标(cursor)就是游动的标识
• 通俗的说,一条sql取出对应n条结果资源的接口/句柄,就是游标,沿着游标可以一次取出一行

cursor	=	conn.cursor()

插入数据

• 对数据库表做修改操作,必须要commit

sql1	=	"insert	into	departments(dep_name)	values(%s)"
result	=	cur.execute(sql1,	('development',))
sql2	=	"insert	into	departments(dep_name)	values(%s)"
data	=	[('hr',),	('op',)]
result	=	cur.executemany(sql2,	data)
sql3	=	"insert	into	departments(dep_name)	values(%s)"
data	=	[('行政',),	('财务',),	('运营',)]
result	=	cur.executemany(sql3,	data)
conn.commit()

查询数据

• 可以取出表中一条、多条或全部记录

sql4	=	"select	*	from	departments"
cur.execute(sql4)
result	=	cur.fetchone()
print(result)
result2	=	cur.fetchmany(2)
print(result2)
result3	=	cur.fetchall()
print(result3)

移动游标

• 如果希望不是从头取数据,可以先移动游标

cur.scroll(1, mode="ralative")  #当前位置的相对位置
cur.scroll(2, mode="absolute")   #绝对位置
sql5	=	"select	*	from	departments"
cur.execute(sql5)
cur.scroll(3,	mode='absolute')
result4	=	cur.fetchmany(2)
print(result4)

删除记录

• 通过delete删除记录

sql7	=	"delete	from	departments	where	dep_id=%s"
result	=	cur.execute(sql7,	(6,))
print(result)
conn.commit()

完整实现

import pymysql
import json
import random

mysql_host = '127.0.0.1'
mysql_user = 'root'
mysql_pass = '123456'
mysql_dbname = 'TestDB'

class py_mysql:
    def __init__(self,host,user,passwd,dbname):
        self.host = host
        self.passwd = passwd
        self.user = user
        self.dbname = dbname
    #创建表
    def create_tables(self):
        db = pymysql.connect(self.host, self.user, self.passwd, self.dbname)
        # 创建游标用来执行sql语句
        cursor = db.cursor()
        sql = 'show tables'
        cursor.execute(sql)
        data = cursor.fetchall()
        print(data)
        tables = [i[0] for i in data]
        print(tables)
        create_dep = ''
        create_emp = ''
        create_sal = ''
        if 'departments' not in tables:  # 判断当前库中没有departments表
            # 编写需要执行的SQL语句
            create_dep = '''CREATE TABLE departments(
    dep_id INT primary key AUTO_INCREMENT,
    dep_name VARCHAR(20)
)'''
        if 'employees' not in tables:
            create_emp = '''CREATE TABLE employees(
    emp_id INT primary key AUTO_INCREMENT,
    emp_name VARCHAR(20),
    birth_date DATE,
    phone VARCHAR(11),
    email VARCHAR(50),
    dep_id INT,
    FOREIGN KEY(dep_id) REFERENCES departments(dep_id)
    on update cascade on delete cascade 
)'''
        if 'salary' not in tables:
            create_sal = '''CREATE TABLE salary(
    id INT primary key AUTO_INCREMENT,
    date DATE,
    emp_id INT,
    basic INT,
    awards INT,
    FOREIGN KEY(emp_id) REFERENCES employees(emp_id)
    on update cascade on delete cascade 
)'''
        try:
            #执行sql语句
            if create_dep != '':
                cursor.execute(create_dep)
            if create_emp != '':
                cursor.execute(create_emp)
            if create_sal != '':
                cursor.execute(create_sal)
            db.commit()
        except:
            print('数据库表创建错误%s' % cursor.Error.args)
            db.rollback()
        cursor.close()
        db.close()
    def insert_value(self,sql):
        db = pymysql.connect(self.host, self.user, self.passwd, self.dbname)
        # 创建游标用来执行sql语句
        cursor = db.cursor()
        insert_dep = "insert into departments(dep_name) values(%s)"
        hr = ('人事部')
        ops = ('运维部',)
        dev = ('开发部',)
        qa = ('测试部',)
        market = ('市场部',)
        deps = [ops,dev,qa]
        try:
            # 执行sql语句
            cursor.execute(insert_dep, hr)
            cursor.executemany(insert_dep, deps)
            cursor.executemany(insert_dep, [market])
            db.commit()
        except:
            db.rollback()
        cursor.close()
        db.close()
    def delete_value(self):
        db = pymysql.connect(self.host, self.user, self.passwd, self.dbname)
        # 创建游标用来执行sql语句
        cursor = db.cursor()
        delete_dep = 'delete from departments where dep_name=%s'
        try:
            # 执行sql语句
            cursor.execute(delete_dep,('市场部',))
            db.commit()
        except:
            db.rollback()
        cursor.close()
        db.close()
    def select_value(self):
        pass
    def update_value(self):
        pass
if __name__ == '__main__':
    sql = py_mysql(mysql_host,mysql_user,mysql_pass,mysql_dbname)
    sql.create_tables()
    # sql.insert_value('')