欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

pymysql的简单使用

程序员文章站 2022-05-28 16:46:52
...
# -*- 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()

相关标签: pyhton pymysql