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()
上一篇: 一碗稀饭的热量大约是多少在减肥的你知道吗
下一篇: pymysql使用(二)