pymysql的简单使用

 

# -*- coding:UTF-8 -*-
"""
pymysql的简单使用：
创建表
插入数据
"""
import os
import pymysql
import warnings

warnings.filterwarnings("ignore")


# 创建Product表，并插入数据
def Product(db, filename):
    count = 0
    data = open(filename)
    lines = data.readlines()
    cursor = db.cursor()
    sql_create_product_table = "CREATE TABLE IF NOT EXISTS Product(\
                                pId INT UNSIGNED AUTO_INCREMENT, \
                                pCode INT(10) NOT NULL,\
                                pType VARCHAR(40) NOT NULL,\
                                pName VARCHAR(40) NOT NULL,\
                                cost DECIMAL(10,2) DEFAULT NULL,\
                                price DECIMAL(10,2) DEFAULT NULL,\
                                submission_date DATE,\
                                PRIMARY KEY ( pId ))\
                               "
    cursor.execute(sql_create_product_table)
    try:
        for line in lines:
            count = count + 1
            line = line.split()
            if count > 1:
                print(line)
                sql_insert = "INSERT INTO Product\
                            (pCode, pType, pName, cost, price, submission_date)\
                            VALUES\
                            ('%d', '%s', '%s', '%f', '%f', NOW())" % (int(line[0]), line[1], line[2], float(line[3]), float(line[4]))
                cursor.execute(sql_insert)
                db.commit()
    except:
        db.rollback()


# 创建Orders表，并插入数据
def Order(db, filename):
    count = 0
    data = open(filename)
    lines = data.readlines()
    cursor = db.cursor()
    sql_create_order_table = """CREATE TABLE IF NOT EXISTS Orders (
                              pId INT UNSIGNED AUTO_INCREMENT,
                              orderNo INT(10) NOT NULL,
                              custNo INT(10) NOT NULL,
                              orderDate DATE,
                              delivDate DATE,
                              submission_date DATE,
                              PRIMARY KEY ( pId ))
                              """
    try:
        cursor.execute(sql_create_order_table)
        for line in lines:
            count = count + 1
            line = line.split()
            if count > 1:
                print(line)
                sql_insert = "INSERT INTO Orders\
                            (orderNo, custNo, orderDate, delivDate, submission_date)\
                            VALUES\
                            ('%d', '%d', '%s', '%s', NOW())" % (int(line[0]), int(line[1]), line[2], line[3])
                cursor.execute(sql_insert)
                db.commit()
    except:
        db.rollback()


# 创建OrderDetail表，并插入数据
def OrderDetail(db, filename):
    count = 0
    data = open(filename)
    lines = data.readlines()
    cursor = db.cursor()
    sql_create_orderDetail_table = """CREATE TABLE IF NOT EXISTS OrderDetail (
                              pId INT UNSIGNED AUTO_INCREMENT,
                              orderNo INT(10) NOT NULL,
                              itemNo INT(4) NOT NULL,
                              pCode INT(10) NOT NULL,
                              qty INT(10) NOT NULL,
                              discount DECIMAL(10,2) NOT NULL,
                              submission_date DATE,
                              PRIMARY KEY ( pId ))
                              """
    try:
        cursor.execute(sql_create_orderDetail_table)
        for line in lines:
            count = count + 1
            line = line.split()
            if count > 1:
                print(line)
                sql_insert = "INSERT INTO OrderDetail\
                            (orderNo, itemNo, pCode, qty, discount, submission_date)\
                            VALUES\
                            ('%d', '%d', '%d', '%d', '%f', NOW())" % (int(line[0]), int(line[1]), int(line[2]), int(line[3]), float(line[4]))
                cursor.execute(sql_insert)
                db.commit()
    except:
        db.rollback()


def main():
    user = input("please input userName:")
    passwd = input("please input passwd:")
    db = pymysql.connect("127.0.0.1", user, passwd, "sports")
    allfiles = os.listdir(os.getcwd())
    listfile = []
    for file in allfiles:
        if 'txt' in file:
            listfile.append(file)
    for filename in listfile:
        if filename == 'Product.txt':
            Product(db, filename)
        if filename == 'Order.txt':
            Order(db, filename)
        if filename == 'OrderDetail.txt':
            OrderDetail(db, filename)
    db.close()


if __name__ == '__main__':
    main()