mysql数据库操作

 

 

# coding=utf8
from pymysql import connect, cursors
from pymysql.err import OperationalError
import time,random



class DataBase(): 
    def __init__(self, mysql_name):
        try:
            self.conn = connect(host='127.0.0.1',
                                port=3306,
                                user='root',
                                password='111111',
                                db=mysql_name,
                                charset='utf8mb4',
                                cursorclass=cursors.DictCursor
                                )

        except OperationalError as e:
            print(e)

    # 查询数据库
    def select(self, table_name):

        # sql语句之间应该有空格
        sql = 'select * from ' + table_name + ';'
        print(sql)

        cursor = self.conn.cursor()  # 创建游标


        cursor.execute(sql)  # 执行sql
        self.conn.commit()  # 提交

        # 将查询的数据打印出来
        results = cursor.fetchall()
        for row in results:
            print(row)


    # 批量插入数据
    def insert_inspection_list(self, table_name):
        for i in range(1, 3):
            AGE = 10+random.randint(1,20)
            INCOME = 3000+random.randint(1,200)

            create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            update_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())


            sql1 ="insert into "  + table_name + " (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME) values('黄', '志伟'," +str(AGE)+",'女',"+str(INCOME)+");"
            # sql1 = "insert into " + table_name + " (AGE,INCOME) values("+str(AGE) +","+str(INCOME) + ");"


            print(sql1)
            cursor = self.conn.cursor()

            cursor.execute(sql1)
            self.conn.commit()

    # 批量插入数据
    def delete_inspection_list(self, table_name):
        sql2 = "delete from " + table_name + " where FIRST_NAME = '黄' ;"
        print(sql2)
        cursor = self.conn.cursor()

        cursor.execute(sql2)
        self.conn.commit()


    # 关闭数据库
    def close(self):
        self.conn.close()


if __name__ == '__main__':
    tb = DataBase('test')

    tb.select('employee')
    # tb.insert_inspection_list('employee')
    tb.delete_inspection_list('employee')