Converting Python to SQL syntax

Question

I would like to convert the Python to SQL, where applicable.

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()

Particularly, I am interested in this snippet, how do you convert the for loop in Python to SQL?

I'd like to know because someone asked about why this was not in SQL and I am not familiar with the SQL syntax along with looping through the system.

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))

Tengis · Accepted Answer · 2021-05-25 01:51:12Z

1

I assume you can do all of what's inside your loop in SQL. For example:

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

So you don't need to do all

    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))

this part. And just return the results from SQL. Also, It's more efficient to do such operations in SQL because it's specially designed for it.

And as you can see in my SQL example - I'm selecting only what I need which is huge performance improvement. Because selecting with "*" will bite you later if your table has more columns with a lot of data in it.

Reference links more on SQL:

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

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

answered May 25, 2021 at 1:51

Tengis

3673 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mph85 Over a year ago

Gotcha, appreciate that information. So are you suggesting that I get rid of sqlite3 and just import SQL and go with that for everything?

Tengis Over a year ago

no, I'm not suggesting to get rid of sqlite3 - generally, all SQL type DBs such as SQLite, MySQL, MSSQL and PosgreSQL etc. have SQL support. SQL is a query language en.wikipedia.org/wiki/SQL whereas SQLite is database management system that is specifically designed for DBs.

mph85 Over a year ago

ah gotcha, appreciate the response

Tengis Over a year ago

So most of SQL statements should work all those SQL type databases above. However, some of them has slight differences in SQL keywords etc which you can learn more in each specific docs in their official documentation websites. e.g. sqlite.org/docs.html

Collectives™ on Stack Overflow

Converting Python to SQL syntax

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related