sql.py - master - koly24/text_chek

sql.py
100 строк3 KB
Nikolay Dolgov
assistant
06 июн 2025, 07:51
06 июн 2025, 07:51390f3f8
import pyodbc
from datetime import datetime, date, timedelta

host = 'http://localhost:5000'
bd = 'localhost\\SQLEXPRESS01'
diver = 'ODBC Driver 18 for SQL Server'

#host = 'http://marathon-of-steps.ru'
#bd = 'WINDOWS-STD3-40\\SQLEXPRESS'
#diver = 'ODBC Driver 17 for SQL Server'

class Sql:
    def __init__(self, database="bot", server=bd): #RUS\SQLEXPRESS #"WINDOWS-STD3-40\SQLEXPRESS"
        self.cnxn = pyodbc.connect("Driver={"+ diver +"};"
                                   "Server=" + server + ";"
                                "Database=" + database + ";"
                                "Trusted_Connection=yes;" +
                                   "MARS_Connection=Yes" + ";TrustServerCertificate=Yes")
        self.query = "-- {}\n\n-- Made in Python".format(datetime.now()
                                                         .strftime("%d/%m/%Y"))

    def getPage(self, page_id):
        sql_x = ("""SELECT [id]
            ,[id_page]
            ,[text]
            ,[text_clean]
            ,[normal_text]
            ,[title]
            FROM [dbo].[my_bot] where [id_page] = ?""")
        cursor = self.cnxn.cursor()
        rows = cursor.execute(sql_x, page_id).fetchall()

        arr = []
        for row in rows:
            jsonObj = {}
            jsonObj['id'] = row[0]
            jsonObj['id_page'] = row[1]
            jsonObj['text'] = row[2]
            jsonObj['text_clean'] = row[3]
            jsonObj['normal_text'] = row[4]
            jsonObj['title'] = row[5]

            arr.append(jsonObj)

        return arr

    def getPages(self):
        sql_x = ("""SELECT [id]
            ,[id_page]
            ,[text]
            ,[text_clean]
            ,[normal_text]
            ,[title]
            FROM [dbo].[my_bot]""")
        cursor = self.cnxn.cursor()
        rows = cursor.execute(sql_x).fetchall()

        arr = []
        for row in rows:
            jsonObj = {}
            jsonObj['id'] = row[0]
            jsonObj['id_page'] = row[1]
            jsonObj['text'] = row[2]
            jsonObj['text_clean'] = row[3]
            jsonObj['normal_text'] = row[4]
            jsonObj['title'] = row[5]

            arr.append(jsonObj)

        return arr

    def addPage(self, id_page, text, title):
        sql_x = ("""INSERT INTO [dbo].[my_bot] VALUES(NEWID(), ?,?,Null,Null, ?)""")
        cursor = self.cnxn.cursor()
        cursor.execute(sql_x, id_page, text, title)
        cursor.commit()
        return True

    def deletePages(self, string):
        sql_x = "DELETE FROM [dbo].[my_bot] where [id_page] not IN (" + string + ")"
        cursor = self.cnxn.cursor()
        cursor.execute(sql_x)
        cursor.commit()
        return True

    def updatePageClean(self, id, clean):
        sql_x = ("""update [dbo].[my_bot] set [text_clean] = ?
                   WHERE [id] = ? """)
        cursor = self.cnxn.cursor()
        cursor.execute(sql_x, clean, id)
        cursor.commit()
        return True

    def updatePageCleanNormal(self, id, clean):
        sql_x = ("""update [dbo].[my_bot] set [normal_text] = ?
                   WHERE [id] = ? """)
        cursor = self.cnxn.cursor()
        cursor.execute(sql_x, clean, id)
        cursor.commit()
        return True