将Python转换为SQL语法

Question

如果适用，我想将Python转换为SQL

import sqlite3
from sqlite3 import Error
from datetime import datetime
import time

# CONSTANTS

FILE = "messages.db"
PLAYLIST_TABLE = "Messages"

class DataBase:
    """
    used to connect, write to and read from a local sqlite3 database
    """
    def __init__(self):
        """
        try to connect to file and create cursor
        """
        self.conn = None
        try:
            self.conn = sqlite3.connect(FILE)
        except Error as e:
            print(e)

        self.cursor = self.conn.cursor()
        self._create_table()

    def close(self):
        """
        close the db connection
        :return: None
        """
        self.conn.close()

    def _create_table(self):
        """
        create new database table if one doesn't exist
        :return: None
        """
        query = f"""CREATE TABLE IF NOT EXISTS {PLAYLIST_TABLE}
                    (name TEXT, content TEXT, time Date, id INTEGER PRIMARY KEY AUTOINCREMENT)"""
        self.cursor.execute(query)
        self.conn.commit()

    def get_all_messages(self, limit=100, name=None):
        """
        returns all messages
        :param limit: int
        :return: list[dict]
        """
        if not name:
            query = f"SELECT * FROM {PLAYLIST_TABLE}"
            self.cursor.execute(query)
        else:
            query = f"SELECT * FROM {PLAYLIST_TABLE} WHERE NAME = ?"
            self.cursor.execute(query, (name,))

        result = self.cursor.fetchall()

        # return messages in sorted order by date
        results = []
        for r in sorted(result, key=lambda x: x[3], reverse=True)[:limit]:
            name, content, date, _id = r
            data = {"name":name, "message":content, "time":str(date)}
            results.append(data)

        return list(reversed(results))

    def get_messages_by_name(self, name, limit=100):
        """
        Gets a list of messages by user name
        :param name: str
        :return: list
        """
        return self.get_all_messages(limit, name)

    def save_message(self, name, msg):
        """
        saves the given message in the table
        :param name: str
        :param msg: str
        :param time: datetime
        :return: None
        """
        query = f"INSERT INTO {PLAYLIST_TABLE} VALUES (?, ?, ?, ?)"
        self.cursor.execute(query, (name, msg, datetime.now(), None))
        self.conn.commit()

特别是，我对这个片段感兴趣，如何将Python中的for循环转换为SQL

我很想知道，因为有人问为什么这不在SQL中，我不熟悉SQL语法以及系统中的循环

def _create_table(self):
        """
        create new database table if one doesn't exist
        :return: None
        """
        query = f"""CREATE TABLE IF NOT EXISTS {PLAYLIST_TABLE}
                    (name TEXT, content TEXT, time Date, id INTEGER PRIMARY KEY AUTOINCREMENT)"""
        self.cursor.execute(query)
        self.conn.commit()

    def get_all_messages(self, limit=100, name=None):
        """
        returns all messages
        :param limit: int
        :return: list[dict]
        """
        if not name:
            query = f"SELECT * FROM {PLAYLIST_TABLE}"
            self.cursor.execute(query)
        else:
            query = f"SELECT * FROM {PLAYLIST_TABLE} WHERE NAME = ?"
            self.cursor.execute(query, (name,))

        result = self.cursor.fetchall()

        # return messages in sorted order by date
        results = []
        for r in sorted(result, key=lambda x: x[3], reverse=True)[:limit]:
            name, content, date, _id = r
            data = {"name":name, "message":content, "time":str(date)}
            results.append(data)

        return list(reversed(results))

Answer 1

我假设您可以在SQL中完成循环中的所有操作。例如：

SELECT "name", "message", "time" from "Messages" ORDER BY "time" DESC LIMIT 100;

所以你不需要做所有的事情

    results = []
    for r in sorted(result, key=lambda x: x[3], reverse=True)[:limit]:
        name, content, date, _id = r
        data = {"name":name, "message":content, "time":str(date)}
        results.append(data)

    return list(reversed(results))

这部分。只需从SQL返回结果。此外，在SQL中执行此类操作更有效，因为它是专门为SQL设计的

正如您在我的SQL示例中所看到的，我只选择了我需要的，这是巨大的性能改进。因为如果您的表中有更多包含大量数据的列，那么使用“*”进行选择会对您产生不利影响

有关SQL的更多参考链接：

https://www.w3schools.com/sql/sql_top.asp

https://www.w3schools.com/sql/sql_orderby.asp