mysql网络数据库操作模块_pymsql模块操作MySQL数据库

表结构

⽤用户(user)表：⽤用户id(user_id),⽤用户名(user_name),密码(password)

订单(orders)表：订单编号(order_id)，下单时间(order_time)

⽀支付⾦金金额(payment)，⽤用户id(uid)

''' user '''

import pymysql

conn=pymysql.connect(host="127.0.0.1",user='root',password='123',database='demo')

cur=conn.cursor(pymysql.cursors.DictCursor )# 数据库操作符 游标

"创建表"

sql="create table user(user_id int primary key auto_increment,user_name char(10) not null,password varchar(25) not null)"

cur.execute(sql)

“数据自动写入表”

with open(r"F:\user",mode="r",encoding="utf-8") as f:

for line in f:

a,b,c=line.strip().split(",")

sql="insert into user(user_name,password) values(%s, %s)"

cur.execute(sql,(b,c))

conn.commit()

conn.close()

'''orders'''

import pymysql

conn=pymysql.connect(host="127.0.0.1",user='root',password='123',database='demo')

cur=conn.cursor(pymysql.cursors.DictCursor )# 数据库操作符 游标

"创建表"

sql="create table orders(order_id int primary key auto_increment,order_time datetime,payment float(10.2),uid int ,foreign key(uid) references user(user_id))"

cur.execute(sql)

“数据自动写入表”

with open(r"F:\orders",mode="r",encoding="utf-8") as f:

for line in f:

a,b,c,d=line.strip().split(",")

sql="insert into orders(order_time,payment,uid) values(%s, %s,%s)"

cur.execute(sql,(b,c,d))

conn.commit()

conn.close()

SQL注入