import sqlite3
from sqlite3 import Error
from time import sleep, ctime
def post_sql_query(sql_query):
with sqlite3.connect('my.db') as connection:
cursor = connection.cursor()
try:
cursor.execute(sql_query)
except Error:
pass
result = cursor.fetchall()
return result
def create_tables():
users_query = '''CREATE TABLE IF NOT EXISTS USERS
(user_id INTEGER PRIMARY KEY NOT NULL,
username TEXT,
first_name TEXT,
last_name TEXT,
reg_date TEXT);'''
post_sql_query(users_query)
def register_user(user, username, first_name, last_name):
user_check_query = f'SELECT * FROM USERS WHERE user_id = {user};'
user_check_data = post_sql_query(user_check_query)
if not user_check_data:
insert_to_db_query = f'INSERT INTO USERS (user_id, username, first_name, last_name, reg_date) VALUES ({user}, "{username}", "{first_name}", "{last_name}", "{ctime()}");'
post_sql_query(insert_to_db_query )
create_tables() # вызываем функцию создания таблицы users
@bot.message_handler(commands=['start'])
def start(message):
register_user(message.from_user.id, message.from_user.username,
message.from_user.first_name, message.from_user.last_name)
bot.send_message(message.from_user.id, f'Welcome {message.from_user.first_name}' )
/usr/bin/python3 /usr/local/bin/bot/bot.py
python3 -V
city = message.text
with db.connect('your.db') as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM table WHERE row = ?", (city,))
result = cursor.fetchall()
def check_city(city ):
with db.connect('your.db') as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM table WHERE row = ? ", (city,))
data = cursor.fetchone()
if data is None:
return False
else:
return data
test = check_city(message.text)
if test:
bot.send_message(user_id, test[0]) # например первое поле из таблицы
line 55 requests.exceptions.MissingSchema: Invalid URL '': No schema supplied. Perhaps you meant http://?
with db.connect(db) as connection:
cursor = connection.cursor()
cursor.execute('''CREATE TABLE USERS (
user_id TEXT PRIMARY key,
username TEXT,
first_name TEXT,
last_name TEXT);'''
def add_user_info(user):
with db.connect(db) as connection:
cursor = connection.cursor()
try:
cursor.execute('''INSERT INTO USERS (user_id, username, first_name, last_name)
VALUES(?,?,?,?);''', user)
except Error:
pass
import sqlite3 as db
from sqlite3 import Error
import time
# handler по команде старт передаст нужные поля из объекта message в функцию добавления юзера в дб
@bot.message_handler(commands=['start'])
def start(message):
tupp = (message.from_user.id, message.from_user.username, message.from_user.first_name, message.from_user.last_name, time.ctime())
send_user_info(tupp)
# один раз запускаешь для создания БД и таблицы в ней
with db.connect('bot_db') as connection:
cursor = connection.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS USERS (user_id INTEGER NOT NULL PRIMARY KEY, first_name TEXT, last_name TEXT, username TEXT)''')
# вызываемая функция
def send_user_info(tup):
connection = db.connect('bot_db'))
cursor = connection.cursor()
try:
cursor.execute("INSERT INTO USERS (user_id, username, first_name, last_name, reg_date) VALUES(?,?,?,?,?)", tup)
except Error:
pass
connection.commit()
connection.close()