python-python操作mysql数据库

1.数据库的连接

链接：https://pan.baidu.com/s/1aAz8tq2O18jv6uZuihKn7A 
提取码：6666

数据在这里，需要自行解压导入到数据库里

#连接数据库，主机地址(ip地址)，端口号，用户名，密码
from pymysql import connect,Error
from pymysql.cursors import DictCursor

conn=None
try:
    conn=connect(
            host='localhost',
            user='root',
            password='Wdd301912',
            database='atguigudb',
            port=3306,
            cursorclass=DictCursor)

    
    print(conn)
    conn.close()
except Error as e:
    print('连接失败:{}'.format(e))

2.数据的增删改查

2.1 增删改查

使用游标对数据进行定位操作
#查询数据库的数据-增删改查-游标
with conn:
#获取到游标对象
    with conn.cursor() as cursorr:
        #进行增删改查
        sql='select * from employees;'
        conn.ping(reconnect=True)
        cursorr.execute(sql)
        #获取游标处理结果
        for item in cursorr:
            print(item['employee_id'])

with conn:
#获取到游标对象
    with conn.cursor() as cursorr:
        #进行增删改查
        sql='select * from employees;'
        conn.ping(reconnect=True)
        cursorr.execute(sql)
        #检索全部数据
        all_data=cursorr.fetchall()
        for item in all_data:
            print(item)
        #检索一定量的数据，重复代表分页
        many_data=cursorr.fetchmany(10)
        for item in many_data:
            print(item)
        print('-'*20)
        many_data=cursorr.fetchmany(10)
        for item in many_data:
            print(item) 

#查询mysql数据
with conn:
#获取到游标对象
    with conn.cursor() as cursorr:
        #进行增删改查
        sql='select * from employees where salary=4200.0;'
        conn.ping(reconnect=True)
        cursorr.execute(sql)
        #检索全部数据
        all_data=cursorr.fetchall()
        for item in all_data:
            print(item)
#对于查询数据，需要用户传递相应的参数之后，执行相应的查询语句，这里就会存在SQL注入风险

#修改mysql数据
with conn:
#获取到游标对象
    with conn.cursor() as cursorr:
        #进行增删改查
        sql="update employees set first_name='Stevenmysql' where employee_id=100;"
        conn.ping(reconnect=True)
        cursorr.execute(sql)
        conn.commit()
        #检索全部数据
        all_data=cursorr.fetchall()
        for item in all_data:
            print(item) 

#新增mysql数据
with conn:
    with conn.cursor() as cursorr:
        #进行增删改查
        job_id1='AC_MG'
        job_title1='Accounting Manager'
        min_salary1=8799
        max_salary1=1600
        
        job_id=str(job_id1)
        job_title=str(job_title1)
        min_salary=int(min_salary1)
        max_salary=int(max_salary1)
        
        sql="insert into `jobs`(job_id,job_title,min_salary,max_salary)values(%s,%s,%s,%s);"
        conn.ping(reconnect=True)
        cursorr.execute(sql,(job_id,job_title,min_salary,max_salary))
        conn.commit() 

#删除mysql数据

class PyMysqllearn(object):
    #获取数据库的连接对象
    def get_connection(self):
            self.conn=None
            conn=connect(
            host='localhost',
            user='root',
            password='Wdd301912',
            database='atguigudb',
            port=3306,
            cursorclass=DictCursor)
            return conn
    
    def close_connect(self):
        try:
            if self.conn is not None:
                self.conn.close()
        except Exception as e:
            print(f'数据库关闭失败{e}')

    def get_one_data(self):
        with conn.cursor() as cursorr:
        #进行增删改查
            sql='select * from employees;'
            conn.ping(reconnect=True)
            cursorr.execute(sql)
            #检索一条数据
            one_data=cursorr.fetchone() 
        return one_data
        
    def get_one_delete(self,row_id):
        row_id=100
        sql="delete from jobs where id=%s"
        cursorr.execute(sql,(row_id,))
        conn.commit()
        print('删除成功')
        
def main():
    obj=PyMysqllearn()   
    result=obj.get_one_data()
    print(result) 
    obj.close_connect()
    obj.get_one_delete()

if __name__ =='__main__':
    main()

​获取数据库指定列数据

获取字典类型的数据列表

2.2 其他补充

2.2.1 SQL注入

SQL注入风险：当需要用户传入相应的值的时候用户会传入一些恶意的mysql语句，从而导致相应数据库的数据信息丢失或者暴露。

SQL注入风险解决：

采用格式化数据，使得相应SQL语句无法注入。

2.2.2 事务与回滚

事务：处理事件的一系列SQL代码

回滚：为了防止事务出错而撤销指定 SQL 语句的过程

代码演示：

#连接数据库，主机地址(ip地址)，端口号，用户名，密码
from pymysql import connect,Error
from pymysql.cursors import DictCursor

conn=None
try:
    conn=connect(
            host='localhost',
            user='root',
            password='Wdd301912',
            database='atguigudb',
            port=3306,
            cursorclass=DictCursor)
    
    with conn:
        with conn.cursor() as cursorr:
        #进行增删改查
            job_id1='AC_MGbv'
            job_title1='Accounting Manager'
            min_salary1=8796
            max_salary1=1607
            
            job_id=str(job_id1)
            job_title=str(job_title1)
            min_salary=int(min_salary1)
            max_salary=int(max_salary1)
            
            sql="insert into `jobs`(job_id,job_title,min_salary,max_salary)values(%s,%s,%s,%s);"
            
            
            sql1="update employees set first_name='Stevenmysql' where employee_id=101;"
            
            conn.begin()
            try:
                conn.ping(reconnect=True)
                cursorr.execute(sql,(job_id,job_title,min_salary,max_salary))
            except Exception as e:
                print(e)
                conn.rollback()
            try:
                conn.ping(reconnect=True)
                cursorr.execute(sql1)
            except Exception as e:
                print(e)
                conn.rollback()
            
            conn.commit()
            conn.close()
except Error as e:
    print('连接失败:{}'.format(e))