数据库---MySql（二）-pymysql

一、安装

pip install pymysql

 

二、使用

1.Python DB API访问数据库流程

2.mysql

2.1 导入mysql模块

import pymysql

2.2 建立连接

使用connect/Connection/Connect这三个建立连接都可以，前两个是后一个函数的引用

mysql.connect(host,port,user,password,database,charset)

• host：连接的mysql主机，如果是本机就是localhost
• port：连接的主机端口，默认是3306
• user：连接mysql的用户名
• password：连接mysql的密码
• database：连接的数据库名称
• charset：连接使用的编码方式，建议使用utf8

connect = pymysql.connect('localhost','root','','db1')

2.3 获取Cursor对象

通过连接获取游标， cursor = connect.cursor()返回Cursor对象，用于执行sql语句并获得结果

cursor = connect.cursor()  #后面用fetch获取的结果是元组里面套元组
cursor = connect.cursor(cursor = pymysql.cursors.DictCursor)  #后面用fetch获取的结果是列表里面套字典

2.4操作数据库

2.4.1使用游标执行SQL语句

cursor.execute(operation , [parameters])，返回受影响的条数（int)

sql = "select * from t1"
cursor.execute(sql)

如果需要上传参数，不要在SQL语句后拼接字符串，防止SQL注入；

sql = "select * from userinfo where name = %s password=%s" %('"" or 1=1 --',"")   #user是空,or 1=1必然成立，--表示注释，后面部分被注释；userinfo表中并没有user是空的数据;SQL注入
c= cursor.execute(sql)
if c:
    print("success")  #结果会打印success
data = cursor.fetchall()
print(data)  #打印userinfo表里的所有数据

需要上传参数，在execute后面传入参数，可以是列表、元组、字典

sql= "insert into userinfo (name,password) values (%s,%s)"
cursor.execute(sql,["小王","123456"])  #传入一条数据
curaor.executemany(sql,(["小张","123456"]，["小刘","123456"]))  #传入多条数据
connect.commit()  #如果是修改表的必须要提交

#传入字典
sql= "insert into userinfo (name,password) values (%(u)s,%(p)s)"
cursor.execute(sql,{"u":"小明","p":"123456"})  #传入一条数据
connect.commit()

2.4.2提交数据、撤销数据

connect.commit()：提交数据；如果修改了数据库必须提交数据

connect.rollback()：撤销数据；在执行增删改操作时，如果不想提交前面的修改操作，可以使用 rollback 回滚取消操作

2.4.3获取数据

fetch获取查询结果数据，返回一个元组嵌套元组；如果获取cursor时设置了connect.cursor(cursor = pymysql.cursors.DictCursor)，返回一个列表里面嵌套字典；

因为在获取数据时，游标是移动的，所以前面取过的数据，后面不会再取了

curser.fetchone()：获取一条数据

curser.fetchmany(4)：获取4条数据，条数可修改

cursor.fetchall()：获取查询结果的所有数据；如果游标不在开头，就不能获取到所有数据

cursor.scroll(2, mode = "relative")：相对位移；在当前位置移动2位，fetch取当前位置后面的第3位数据；数字可变

cursor.scroll(2, mode = "absolute")：绝对位移；移动2位，fetch始终取第3位的数据；数字可变

cursor.lastrowid：获取插入数据的最后一条的自增id

cursor.description：方法会将每个字段的字段名,字段类型,字段长度...等等字段的属性列出来

2.5 关闭cursor

cursor.close()

2.6 关闭连接

connect.close()

 

三、练习

1.创建表

import pymysql


connect = pymysql.connect('localhost','root','','db1')
cur = connect.cursor()
# cur = connect.cursor(cursor = pymysql.cursors.DictCursor)
#创建表
sql = """create table userinfo(
     id int unsigned not null auto_increment primary key,
     name char(10) not null,
     password char(10) not null
     )engine=innodb default charset=utf8;"""
try:
    cur.execute(sql)
    connect.commit()   #提交
except Exception as e:
    print("错误信息：%s" %e)
    connect.rollback()   #回滚




#显示表
sql_s = "show tables;"
cur.execute(sql_s)
data = cur.fetchall()
print(data)
cur.close()
connect.close()

2.插入数据和查询

import pymysql
import pandas

connect = pymysql.connect("localhost","root","","db1")
cur = connect.cursor()
sql_i = "insert into userinfo (name, password) values (%s, %s)"
try:
    cur.executemany(sql_i,(('小明','123456'),
                    ('小红','123456'),
                    ('小张','123456'),
                    ('小王','123456'),
                    ('小小明','123456'),
                    ('Lucy','123456')))
    connect.commit()
except Exception as e:
    print("错误原因：%s" %str(e))
    connect.rollback()

sql_s = "select * from userinfo"
cur.execute(sql_s)
data = cur.fetchall()
print(cur.description)
header = [t[0] for t in cur.description]
df = pandas.DataFrame(list(data),columns = header)
print(df)

 

四、简单封装

import pymysql
import pandas

class MyPymysql:

    def __init__(self,*args):
        self.connect = pymysql.connect(*args)
        self.cur = self.connect.cursor()

    def use(self,db):
        """切换数据库"""
        sql = "use %s;"
        self.cur.execute(sql,db)

    def create_db(self,db):
        """创建数据库"""
        sql = "create database %s;"
        try:
            self.cur.execute(sql,db)
            self.connect.commit()
        except Exception as e:
            print("错误信息：%s" %e)
            self.connect.rollback()

    def show_db(self):
        """显示数据库"""
        sql = "show databases;"
        self.cur.execute(sql)

    def create_table(self,sql):
        """创建数据表"""
        try:
            self.cur.execute(sql)
            self.connect.commit()
        except Exception as e:
            print("错误信息：%s" %e)
            self.connect.rollback()

    def show_t(self):
        """显示表"""
        sql = "show tables;"
        self.cur.execute(sql)

    def insert(self,sql):
        """插入数据"""
        try:
            self.cur.execute(sql)
            self.connect.commit()
        except Exception as e:
            print("错误原因：%s" %e)
            self.connect.rollback()

    def select(self,sql):
        """查询表显示所有结果"""
        try:
            self.cur.execute(sql)
            data = self.cur.fetchall()
            header = [t[0] for t in self.cur.description]
            df = pandas.DataFrame(list(data),columns = header)
        except Exception as e:
            print("错误原因：%s" %e)
        return df

    def change(self,sql):
        """修改表"""
        try:
            self.cur.execute(sql)
        except Exception as e:
            print("错误原因：%s" %e)


if __name__ == "__main__": 
    my = MyPymysql("localhost","root","","db1")

    sql_u = "update userinfo set password = '098765';"
    my.change(sql_u)

    sql = "select * from userinfo limit 3;"
    data = my.select(sql)
    print(data)