【tornado建站】mysql存储与读取数据

最新推荐文章于 2024-05-23 09:37:04 发布

原创最新推荐文章于 2024-05-23 09:37:04 发布 · 388 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#tornado #建站 #python

tornado建站专栏收录该内容

10 篇文章

订阅专栏

本文介绍如何使用Python操作MySQL数据库以支持Tornado框架搭建的网站应用，包括创建数据库和表、插入、查找和删除数据的具体实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

【tornado建站】mysql存储与读取数据

搭建网站，首先需要准备好数据库，我们这里用的是mysql，通过python来查询及插入等操作，我们需要写一个专用于mysql数据库操作的脚本。

操作数据库主要需要有以下几个功能：

import pymysql

class CreateDB：
	# 创建数据库
	pass
class CreateTable：
	# 创建表
	pass
class InsertTable：
	# 插入表
	pass
class FindData：
	# 查找数据
	pass
class DeleteData：
	# 删除数据
	pass

首先是创建数据库的代码：

class CreateDB:
    def __init__(self, db, db_name):
	    self.cursor = db.cursor()
	    self.db_name = db_name

    def run_sql(self):
	    sql = 'CREATE DATABASE %s' % self.db_name
	    self.cursor.execute(sql)
	    print("create database %s ok!" % self.db_name)

然后是创建表的代码：

class CreateTable:

    def __init__(self, db, table_name):
        self.cursor = db.cursor()
        self.table_name = table_name

    def run_sql(self):
        self.cursor.execute("DROP TABLE IF EXISTS %s" % self.table_name)
        sql = """CREATE TABLE %s (
                 name_id INT NOT NULL AUTO_INCREMENT,
                 HEADER  CHAR(100),
                 DATE CHAR(10),
                 TEXT VARCHAR(6000),
                 TAG CHAR(20),
                 PRIMARY KEY(name_id))
                 """ % self.table_name
        self.cursor.execute(sql)
        print("Created table %s!" % self.table_name)

然后是插入表格：

class InsertTable:

    def __init__(self, db, table_name, header, date, text, tag):
        self.db = db
        self.cursor = db.cursor()
        self.my_dic = {"table": table_name, "header": header, "date": date, "text": text, "tag": tag}

    def run_sql(self):
        sql = """INSERT INTO {table}(HEADER, DATE, TEXT, TAG)
                 VALUES ('{header}', '{date}', '{text}', '{tag}')""".format(**self.my_dic)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            print("insert ok!")
        except:
            self.db.rollback()
            print("something wrong during insert!")

接下来是查找数据，这里提供了三种方式，一种是查找所有信息，一种是查找指定信息，另外一种是查找包含信息的条目：

class FindData:

    def __init__(self, db, table_name, method, table_str, find_str):
        self.cursor = db.cursor()
        self.method = method
        self.my_dic = {"table": table_name, "table_str": table_str, "find_str": find_str}

    def run_sql(self):
        if self.method == "all":
            sql = "SELECT * FROM {table}".format(**self.my_dic)
        elif self.method == "one":
            sql = "SELECT * FROM {table} \
                    WHERE {table_str} = '{find_str}'".format(**self.my_dic)
        elif self.method == "ctn":
            sql = "SELECT * FROM {table} \
                    WHERE {table_str} LIKE '%{find_str}%'".format(**self.my_dic)
        try:
            out_puts = []
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            for row in results:
                out_puts.append(list(row))
                print("find ok!")
        except:
            print("Error: unable to fetch data")
        return out_puts

另外添加了一个删除数据的类，必要时使用：

class DeleteData:

    def __init__(self, db, table_name, table_str, find_str):
        self.db = db
        self.cursor = db.cursor()
        self.my_dic = {"table": table_name, "table_str": table_str, "find_str": find_str}

    def run_sql(self):
        sql = "DELETE FROM {table} \
                WHERE {table_str} = '{find_str}'".format(**self.my_dic)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            print("Delete ok!")
        except:
            self.db.rollback()
            print("something wrong during delete!")

这样操作数据库的方式基本都实现了，因为考虑到构建网站需要较多的配置条目，所以单独提取到一个脚本 config.py

# -*- coding: utf-8 -*-

class SqlConfig:

    host = "localhost"
    user = 'test'
    port = 3306
    passwd = 'test'
    dbname = 'TEST'
    table_name = "MY_TEST"
    charset = 'utf8'

所以代码如下：

# -*- coding: utf-8 -*-

import pymysql
import config

# --------------------------------------------------#
# 操作mysql数据库
# 提供了创建数据库、创建表、插入数据、查找数据、删除数据
# 通过main调用
# --------------------------------------------------#
#     main("create_db")
#     main("create_table")
#     main("insert_table", header, date, text, tag)
#     main("find_data", method, table_str, find_str)  # method = "all"/"one"/"ctn"
#     main("delete_data", table_str, find_str)
# --------------------------------------------------#
#


class CreateDB:
    def __init__(self, db, db_name):
        self.cursor = db.cursor()
        self.db_name = db_name

    def run_sql(self):
        sql = 'CREATE DATABASE %s' % self.db_name
        self.cursor.execute(sql)
        print("create database %s ok!" % self.db_name)


class CreateTable:

    def __init__(self, db, table_name):
        self.cursor = db.cursor()
        self.table_name = table_name

    def run_sql(self):
        self.cursor.execute("DROP TABLE IF EXISTS %s" % self.table_name)
        sql = """CREATE TABLE %s (
                 name_id INT NOT NULL AUTO_INCREMENT,
                 HEADER  CHAR(100),
                 DATE CHAR(10),
                 TEXT VARCHAR(6000),
                 TAG CHAR(20),
                 PRIMARY KEY(name_id))
                 """ % self.table_name
        self.cursor.execute(sql)
        print("Created table %s!" % self.table_name)


class InsertTable:

    def __init__(self, db, table_name, header, date, text, tag):
        self.db = db
        self.cursor = db.cursor()
        self.my_dic = {"table": table_name, "header": header, "date": date, "text": text, "tag": tag}

    def run_sql(self):
        sql = """INSERT INTO {table}(HEADER, DATE, TEXT, TAG)
                 VALUES ('{header}', '{date}', '{text}', '{tag}')""".format(**self.my_dic)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            print("insert ok!")
        except:
            self.db.rollback()
            print("something wrong during insert!")


class FindData:

    def __init__(self, db, table_name, method, table_str, find_str):
        self.cursor = db.cursor()
        self.method = method
        self.my_dic = {"table": table_name, "table_str": table_str, "find_str": find_str}

    def run_sql(self):
        if self.method == "all":
            sql = "SELECT * FROM {table}".format(**self.my_dic)
        elif self.method == "one":
            sql = "SELECT * FROM {table} \
                    WHERE {table_str} = '{find_str}'".format(**self.my_dic)
        elif self.method == "ctn":
            sql = "SELECT * FROM {table} \
                    WHERE {table_str} LIKE '%{find_str}%'".format(**self.my_dic)
        try:
            out_puts = []
            # 执行SQL语句
            self.cursor.execute(sql)
            # 获取所有记录列表
            results = self.cursor.fetchall()
            for row in results:
                out_puts.append(list(row))
                print("find ok!")
        except:
            print("Error: unable to fetch data")
        return out_puts


class DeleteData:

    def __init__(self, db, table_name, table_str, find_str):
        self.db = db
        self.cursor = db.cursor()
        self.my_dic = {"table": table_name, "table_str": table_str, "find_str": find_str}

    def run_sql(self):
        sql = "DELETE FROM {table} \
                WHERE {table_str} = '{find_str}'".format(**self.my_dic)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            print("Delete ok!")
        except:
            self.db.rollback()
            print("something wrong during delete!")


def main(command, *args):
    host = config.SqlConfig.host
    user = config.SqlConfig.user
    passwd = config.SqlConfig.passwd
    dbname = config.SqlConfig.dbname
    table_name = config.SqlConfig.table_name
    charset = config.SqlConfig.charset

    if command == "create_db":
        db = pymysql.connect(host, user, passwd, charset=charset)
        sql_type = CreateDB(db, dbname)
    else:
        db = pymysql.connect(host, user, passwd, dbname, charset=charset)
        if command == "create_table":
            sql_type = CreateTable(db, table_name)
        elif command == "insert_table":
            header, date, text, tag = args
            sql_type = InsertTable(db, table_name, header, date, text, tag)
        elif command == "find_data":
            method, table_str, find_str = args
            sql_type = FindData(db, table_name, method, table_str, find_str)
        elif command == "delete_data":
            table_str, find_str = args
            sql_type = DeleteData(db, table_name, table_str, find_str)
        else:
            print("no command named %s" % command)
    result = sql_type.run_sql()

    db.close()
    return result