I didn't know how to make db differently, so I made it in json format, and the people who answered recommended me to make db.
I'm working on the sauce while studying the db I succeeded in saving the video
For each BJ who got the data while crawling, they make a table with the name "db_ID" (Current table about 3,000)
The table consists of [unique number, id, nickname, date, title, length, link, view count]
from telegram import InlineKeyboardButton, InlineKeyboardMarkup, parsemode
from telegram.ext import Updater, MessageHandler, Filters, CommandHandler, CallbackQueryHandler
import time
from functools import partial
import logging
import sqlite3
import sys
formatter = logging.Formatter('[%(levelname)s] %(asctime)s > %(message)s')
file_handler = logging.FileHandler('./telegram_bot_db.log',encoding='utf-8')
stream_handler = logging.StreamHandler()
stream_handler.setFormatter(formatter)
file_handler.setFormatter(formatter)
logger = logging.getLogger("logger")
logger.setLevel(logging.DEBUG)
logger.addHandler(file_handler)
logger.addHandler(stream_handler)
My_token = '#bot token'
sys.setrecursionlimit(10000) #Error, searched and added
conn = sqlite3.connect('af.db') # db connection
cur = conn.cursor()
print('start bot')
def search(bot, update): # Enter bjid in bot
bjid = update.message.text # bjid = searched in bot
l=[]
bjid = bjid.lower() #Convert to lowercase because there are people who automatically capitalize the first letter
chat_id = update.message.chat_id #chat_id = Telegram ID of the person who used the bot
dbid = 'db_'+ bjid # If bj ID starts with a number, an error occurs and db_attaches to the ID when creating a table
cur.execute('select name from sqlite_master where type="table" and name="{}"'.format(dbid))
# Select table name is dbid
aa = cur.fetchall() # If there is a table with a bjid name searched, give it a list with a table name, and if not, return []
# There is only one table per bjid, so fetchall, one, return only one
print(aa) # The problem from here is that there is no printed value
# Ifaa!=': #print results are not available, so comment on the following
# update.message.reply_text ('with table')
# # else:
# update.message.reply_text ('no table')
def get_message(bot, update) :
update.message.reply_text(time.time())
#print(update.message)
update.message.reply_text(update.message.text)
def help_command(bot, update) :
update.message.reply_text ("Contact")
def start(bot, update):
update.message.reply_text ("start")
updater = Updater(my_token)
message_handler = MessageHandler(Filters.text, search)
updater.dispatcher.add_handler(message_handler)
updater.dispatcher.add_handler(CommandHandler('start', start))
help_handler = CommandHandler('help', help_command)
updater.dispatcher.add_handler(help_handler)
updater.dispatcher.add_handler(CommandHandler('search', partial(search, offset=0)))
updater.start_polling(timeout=3, clean=True)
updater.idle()
Def search (bot, update): If you run the source in the bottom part outside the function, print the table name if the aa value is table, and if not, print [].
dbid = 'db_'+ bjid
cur.execute('select name from sqlite_master where type="table" and name="{}"'.format(dbid))
aa = cur.fetchall()
print(aa)
If you type bjid in the Telegram bot, search in db and give them the values if there is a table with the name (when you get BJ's data, you create a table, so if you look up the table, there is the data of BJ)
It doesn't work in the function, but it works well outside the function;; I don't know what's wrong
python sqllite database telegram
Just to tell you the cause, it's a reference error.
def search(bot, update):
bjid = update.message.text
l=[]
bjid = bjid.lower()
chat_id = update.message.chat_id
dbid = 'db_'+ bjid
Cur.execute (blah blah blah)
If you look closely at this source, cur suddenly pops up in line 7 in a method called search().The Python doesn't know what it's referring to, so it's getting an error. As you can see outside the function, there's a cur, so you can refer to it.
But no matter how much you read it, this is not a real problem. As mentioned in the comments above, bj The fact that one person has a table is a serious problem. In most cases, you don't have to 'search' the 'name' of the table. Today's design accumulates vast amounts of data with less close relationships and will constantly block and bother you with what you want to create.
CREATE TABLE bj(
bjid INTEGER PRIMARY KEY,
bjname TEXT,
/* There will be more, but I will skip it */
);
CREATE TABLE bjclip(
clipid INTEGER,
cliptitle TEXT,
clipbj INTEGER,
/* There will be more, but I'll skip the middle
FORREIGN KEY (clipbj) REFERENCES bj (bjid) /* Important */
);
It's much easier to talk from there if the table data structure is like that.
/* When a user enters a BJ's name, the videos taken by the BJ are shown along with the BJ's name */
SELECT *
FROM bjclip
JOIN bj ON bj.bjid = bjclip.clipbj
WHERE bj.bjname LIKE '%User input%';
/* When the user searches for the title of the video, let them know the BJs who uploaded the video of the title */
SELECT *
FROM bj
WHERE bjid IN (
SELECT clipbj
FROM bjclip
WHERE CLIPITLE LIKE '%User Input%'
);
The above queries are always 'result set'. As a result, the size of the set can be either 0 or 1 or more. For Python, you just have to spray the resultant three in a turn. You don't have to dance in a fuss where you acquire a cursor, specify a limit on the number of recursion, and attach an excitation process to let you know, "There are 0 BJs with the name you searched for."
We strongly recommend that you change your data structure design even now. Combine all the created tables into one table, and create a separate table that collects BJ information and relate to each other. That's the fast way.
© 2024 OneMinuteCode. All rights reserved.