数据库读写

数据库回顾

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

一、安装

sudo apt-get install mysql-server python-mysqldb

树莓派

sudo apt-get install mariadb-server

进入数据库

 sudo mysql -u root -p

设置密码

set password for root@localhost = password('123456');
quit;
sudo service mysql restart

再次进入数据库

 sudo mysql -u root -p123456

显示当前拥有的数据库

show databases;

这里只是代表一个库，然后我们需要创建一个表以及定义表结构，首先先选择他

use testdb;

create table person(name varchar(20),health_code varchar(20),rfid1 int,rfid2 int,rfid3 int,rfid4 int);

create table record(into_time datetime,health_code varchar(20),temp double,user_name varchar(20));

接下来以python写下record以及读取person

import pymysql
import datetime
# 1.连接数据库
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="123789",
    database="testdb",
    charset="utf8"
)
 
# 2.创建游标对象
cur = conn.cursor()
 
# 3.操作
try:
 
    now = datetime.datetime.now()
    now = now.strftime("%Y-%m-%d %H:%M:%S")
    dt=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
 

    cur.execute("insert into record(into_time,health_code,temp,user_name) \
                      values('%s','%s','%f','%s')" % \
                       ( dt,"red",23,"tx"))

except Exception as e:
    print("插入数据失败:", e)
else:
    conn.commit()
    if(cur.rowcount > 0):
        print("插入数据成功。")
 
# 4.关闭游标
cur.close()
# 5.关闭连接
conn.close()

'''fetchone'''
import pymysql
#打开数据库连接
conn=pymysql.connect(
    host="localhost",
    user="root",
    password="123789",
    database="testdb",
    charset="utf8"
)
conn.select_db('testdb')
#获取游标
cur=conn.cursor()

cur.execute("select * from record;")
while 1:
    res=cur.fetchone()
    if res is None:
        #表示已经取完结果集
        break
    print (res)
cur.close()
conn.commit()
conn.close()
print('sql执行成功')

insert into person values("duyujie","green",12,12,12,12);
insert into person values("lidongyan","yellow",12,12,12,12)
insert into person values("linbingxin","green",12,12,12,12)
insert into person values("wangxirong","yellow",12,12,12,12);
select name from person where rfid1 =15;

总结

提示：这里对文章进行总结：

例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。