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()