Python 操作数据库

puffer

import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.20.128',port=3306,user='root',passwd='123456',db='test')
# 创建游标
cursor = conn.cursor()
# 执行sql
# cursor.execute("insert into student(name,phone,gender) values ('zhenzhen','13100000007','M')")
# 执行多用户
student_many = [('jany','13100000008','M'),('tony','13100000009','F'),('mark','13100000010','F')]
cursor.executemany("insert into student(name,phone,gender) values (%s,%s,%s)",student_many)
effect_row = cursor.execute('select * from student')
print(cursor.fetchall())
# 提交
conn.commit()
# 关闭游标
cursor.close()
我们可以把sql过程封装一下：
import pymysql

class DB:
    def __init__(self):
        self.conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='123456',db='test')
        self.cursor = self.conn.cursor()

    #析构函数，实例删除时触发
    def __del__(self):
        self.cursor.close()
        self.conn.close()
    #查
    def query(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()
    #提交事务
    def exex(self,sql):
        try:
            self.cursor.execute(sql)
            self.cursor.commit()
        except Exception as e:
            self.conn.rollback()
            print(str(e))
#登录的时候用
    def check_obj(self,obj_name):
        result = self.query("select * from obj where obj_name='{}'".format(obj_name))
        return True if result else False
#删除用户
    def del_user(self,obj_name):
        self.exec("delete from obj where obj_name='{}'".format(obj_name))