建表

在mysql数据库中建立一张表userlist: (不知道为什么保存的时候height, weight的值会变)

数据库连接

# connect to mysql
conn = sql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    passwd = 'toor',
    db = 'pizzaordersys',
)

#get cursor
cur = conn.cursor()

插入数据

tm = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) #获取当前时间
insert = "insert into userlist values(id,%s,%s,%s,%s,%s,%s);"  # '%s'的写法是错误的, 不要忘记加分号
result = cur.execute(insert, (name, gender, height, weight, IBW, tm))  #都是前面的数据
conn.commit()  #对表有改动必须要commit()才行

if result != 0:
    print("successful!")

数据库中也增加了一条数据: (不知道为什么weight的类型会自动补零而且还改不过来)

查找数据

(一)显示所有数据

#show all information
select = "select * from userlist;"
result = cur.execute(select)
if result!=0:
    print("select - successful!")
    for row in cur.fetchall():
        print(row)

(二)根据条件显示部分数据

ids = list(map(int,input("input ids:").split()))
print(ids)

for i in ids:
    select = "select * from userlist where id in (%s);"
    result = cur.execute(select,i)
    print(cur.fetchone())

改动

name ="xixi"
newname ="apple"

update = "update userlist set name = %s where name= %s;"
result = cur.execute(update,(newname,name))

if result!=0:
    print("update-successful!")

结果: 所有原来name为'xixi'的都被改成了'apple'

删除

name ="popo"

delete = "delete from userlist where name = %s;"
result = cur.execute(delete,name)
conn.commit()

if result!=0:
    print("delete - successful!")

删除前: 删除后:

关闭连接

#close the connection
cur.close()
conn.close()

其他有用的知识

参考: python连接数据库 十分钟教你python+MySQL数据库操作

cur.fetchone()    #获取下一个查询结果

cur.fetchmany(4)  # 获取指定个数个查询结果

cur.fetchall()     # 获取所有的查询结果

cur.rowcount   # sql语句影响的行数

表中插入多条数据:

除了用一条条用execute( )插入之外，我们还可以用executemany（）的方式批量插入，也就是val中包含的是一个元组列表，包含我们想要插入的数据。

# 往表里插入一些记录  
sql="INSERT INTO customers(name,address,sex,age,sl) VALUES(%s, %s,%s,%s,%s)"
val = ("John", "Highway 21","M",23,5000)  
mycursor.execute(sql, val)  
val = ("Jenny", "Highway 29","F",30,12500)  
mycursor.execute(sql, val)  
val=[("Tom","ABC 35","M",35,14000),  
     ("Tom1","Highway 29","M",28,6700),  
     ("Lily","Road 11","F",30,8000),  
     ("Martin","Road 24","M",35,14000),  
     ("Sally","Fast 56","M",32,15000)]  
mycursor.executemany(sql, val)  
con.commit() 

另一个插入数据的例子:

 # store user details into the database ibw
    tm = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    store = "insert into userlist values(id,%s,%s,%s,%s,%s,%s);"

    height = heightInch*2.54
    result = cur.execute(store, (name, gender, height, weightKilo, IBW, tm))
    conn.commit()

    if result!=0:
        print("your information has been updated in our database. Thank you!")