tornado数据库操作 torndb

tornado数据库操作

安装准备, 默认学习完tornado基础

pip install mysql==0.7.7
pip install torndb_for_python3

单表操作

import torndb_for_python3 as torndb

"""  单表操作
mysql> desc t_auser;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| userid  | int(20)     | NO   | PRI | NULL    | auto_increment |
| uname   | varchar(16) | YES  |     | NULL    |                |
| pwd     | varchar(16) | YES  |     | NULL    |                |
| created | datetime    | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
"""
conn = torndb.Connection(host="127.0.0.1:3306", database="tornado2021", user="root", password="1234", time_zone="+8:00")
print(conn)


def insertUser():  # 插入操作
    # 1. 增加一条记录
    # 方式一, 可能可以防止sql注入
    rowid = conn.insert('insert into t_auser values (null, %s, %s, now())', 992, "abcde")  # 注意这里的写法,参数写出元组会报错
    # 方式二, 自动拼接可能不会防止sql注入
    # conn.insert('insert into t_auser values (null, "%s", "%s", now())' % ('tom', 'pwd1234'))
    print(rowid)


def insertMany(args=None):  # 一次插入多条
    if args is None:
        args = []
    sql = 'insert into t_auser values (null, %s, %s, now())'
    rowid = conn.insertmany(sql, args)
    print(rowid)
# insertMany([("zj", '123'), ("lb", 234)])


def queryUser():  # 查询所有
    sql = "select * from t_auser"
    users = conn.query(sql)
    for u in users:
        print(u)


def queryOne(): # 查询一个
    sql = "select * from t_auser where uname=%s and pwd=%s"
    user = conn.query(sql,'zj', '123')
    print(user)

def likeQuery(): # 模糊查询 注意sql的写法，这个时候变量名要加双引号
    sql = 'select * from t_auser where uname like "{uname}%%"'.format(uname='z')  # 两个%会转义成一个%
    user = conn.query(sql)
    print(user)

def orderBy(rule:str):  # 排序， 参数rule是一个字段，如果字段前带了一个负号就表示倒序
    order = "ASC"  # 默认升序
    if rule.startswith("-"):
        order = "DESC"  # 倒序
        rule = rule[1:]
    sql = "select * from t_auser order by %s %s" % (rule, order)
    print(sql)
    user = conn.query(sql)
    for u in user:
        print(u)
# orderBy("-uname")


# 分页 limit 用于指定查询结果从哪条记录开始显示，一共显示多少条记录。
# limit 3, 5; --> 表示从第四条记录开始显示，共显示5条

conn.close()

多表操作

import torndb_for_python3 as torndb

# 多表操作
"""
班级表
create table t_cls (cno int(8) primary key auto_increment, cname varchar(16));
学生表
create table t_student (sno int(8) primary key auto_increment, sname varchar(16), cno int(8) not null references t_cls(cno));
课程表
create  table t_course  (courseid int(8) primary key auto_increment, coursename varchar(8)); 
学生与课程之间的关系是多对多, 所以需要一张中间表
create table t_sc (id int(8) primary key auto_increment, sno int(8) not null references t_student(sno), courseid int(8) not null references t_course(courseid));
"""


def insertDB(clsname, sname, coursename=None):
    # clsname班级 sname学生 coursename该学生要上的课程列表
    # 向数据库中插入该学生要上的所有课程
    if coursename is None:
        coursename = []
    # 1. 建立连接
    conn = torndb.Connection(host="127.0.0.1", database="tornado2021", user='root', password="1234")
    # 2. 插入班级表
    classList = conn.query("select cno from t_cls where cname=%s", clsname)
    if classList:  # 如果该班级已经存在
        classid = classList[0]['cno']  # 取出其id
    else:  # 不存在就插入
        classid = conn.insert("insert into t_cls values (null, %s)", clsname)
    print("该班级id为: ", classid)

    # 3. 插入学生表
    stuList = conn.query("select sno from t_student where sname=%s", sname)
    if stuList:
        stuid = stuList[0]["sno"]
    else:
        stuid = conn.insert("insert into t_student values (null, %s, %s)", sname, classid)

    # 4. 插入课程表, 一个学生需要上多门课程, coursename是一个列表
    courseIdList = []
    for course in coursename:
        courseList = conn.query("select courseid from t_course where coursename=%s", course)
        if courseList:
            courseid = courseList[0]["courseid"]
        else:
            courseid = conn.insert("insert into t_course values (null, %s)", course)
        courseIdList.append(courseid)

    # 5. 插入学生与课程之间的中间表
    for course_id in courseIdList:
        res = conn.query("select id from t_sc where sno=%s and courseid=%s", stuid, course_id)
        if not res:
            res = conn.insert("insert into t_sc values (null, %s, %s)", stuid, course_id)
        print("中间表t_sc: ", res)

    # 6. 断开连接
    conn.close()

insertDB("python", 'liming', ["html5", "css3", 'js'])