data_access.py

data_access.py

#!/usr/bin/env python

from random import randint
import sys
import mysql.connector

NUM_EMPLOYEES = 10


def init_db():
    cursor = connection.cursor()
    try:
        pass
    except:
        pass
    cursor.close()


def insert():
    cursor = connection.cursor()
    try:
        cursor.execute("drop table employees")
    except:
        pass
    cursor.execute(
        "create table employees (id integer primary key, name text)")
    cursor.close()

    print("Inserting employees...")
    for n in range(0, NUM_EMPLOYEES):
        cursor = connection.cursor()
        cursor.execute("insert into employees (id, name) values (%d, 'Employee_%d')" %
                       (n, n))
        connection.commit()
        cursor.close()


def select():
    print("Selecting employees...")
    while True:
        cursor = connection.cursor()
        cursor.execute("select * from employees where name like '%%%d'" %
                       randint(0, NUM_EMPLOYEES))
        # for test
        # desc = cursor.description  # 获取字段的描述，默认获取数据库字段名称，重新定义时通过AS关键重新命名即可
        # data_dict = [dict(zip([col[0] for col in desc], row))
        #              for row in cursor.fetchall()]
        # print(data_dict)
        for row in cursor:
            pass
        cursor.close()

#


def delete():
    print("deleting employees...")
    # 随机删除直到没有
    while True:
        for i in range(0, NUM_EMPLOYEES):
            try:
                cursor = connection.cursor()
                cursor.execute("delete from employees where id = '%d'" % randint(0, NUM_EMPLOYEES))
                connection.commit()
            except:
                pass
        cursor.close()


# update
def update():
    print("updating employees...")
    # 一直不断随机更新
    while True:
        cursor = connection.cursor()
        cursor.execute("update employees set name = 'ployee_%d' WHERE name like '%%%d'"
                %(randint(0, NUM_EMPLOYEES),randint(0, NUM_EMPLOYEES)))
        connection.commit()
        cursor.close()


connection = mysql.connector.connect(
    host='127.0.0.1', database='test', user='root', password='123')

if "insert" in sys.argv:
    while True:
        insert()
elif "insert_once" in sys.argv:
    insert()
elif "select" in sys.argv:
    select()
elif "delete" in sys.argv:
    delete()
elif "update" in sys.argv:
    update()
else:
    print("USAGE: data_access.py <insert|insert_once|select|delete|update>")

connection.close()