I was inspired to write this article by the desire to help the same newcomers to Python in general and to work with Flask in particular, as I myself. While working on the task of a holistic and understandable explanation in the style that we, newcomers, did not find, like. I had to look for the information bit by bit. There will be no pictures. A purely technical article. Experienced people will be grateful for comments and for tips on improving the code.So let's get started.I started learning Python right after the New Year. After four months, I realized that the theory, as well as training in fictional tasks that do not go into prod, you won’t especially learn, I decided to look for “combat” tasks. To do this, I asked my friends if they had any real tasks that needed to be programmed. One “my friend friend” asked me to implement a bot for Telegram (I miss the essence of the bot; it doesn’t matter - I needed to take any task that has a specific customer and implement it as the customer wants).In search of a solution, it is natural that the first thing I started with is the well-known Python Telegram Bot and pyTelegramBotAPI frameworks.. At first, as a beginner, it seemed to me just a find - it was possible especially without understanding the nuances of the code to quickly start “sawing” the real bot. For a couple of days I came across the fact that I can’t create the functionality I need. It seems that he did everything according to the documentation, but it was not decided. Then I realized that I did not understand at all how the framework works “under the hood” and why something that should work doesn’t work for me; where and what commands should be called and with what methods. In general, I decided to put aside the framework and try to understand more deeply how the Telegram API itself works and how you can work with it directly, which will give me more control over the situation, and will also allow me to study more closely the whole kitchen of working with the API. It is possible that I will not return to using the Python Telegram Bot and pyTelegramBotAPI frameworks as unnecessary.Or maybe I’ll come back to simplify my work on creating my own bike with Telegram API. But even if I return, I will understand much more about the work of these frameworks.I had an unreviewed little course on Udemy just to create a bot for Telegram. At the time of this writing, this was the only Udemy course where a person would solve a problem without the Python Telegram Bot and pyTelegramBotAPI (I won’t give a link so that it would not be an advertisement). To solve it, he used Flask. Here, by the way, after going through a certain “military path” I had the opportunity to write down my course on this topic, although, of course, it is too early - I won’t bring any special value. But if you, a more experienced programmer reading this article, know a lot about this, then you can create your own course and I’ll be happy to buy it from you for $ 10.99 (a typical “discount” price for Udemy) to learn something new.In general, from the course, I realized that Flask will allow me to make life easier for processing GET and POST requests.Since this article is specifically devoted to working with the database, I will talk about this. Although, all the time, it’s tempting to describe other subtleties, such as: transferring the connection settings to a “secret” file, receiving and processing data received from the Telegram API, how to receive webhooks from Telegram on a local computer without an ssl certificate. If you have interest, then let me know and I will write a separate article.About 3-4 days of my June time it took me to understand that I needed to move away from the framework before I could confidently receive data from the Telegram, process the text (parsing), and depending on what the user wrote, send him the necessary commands, as well as buttons (including overwrite the message and change the buttons).The next step for me was to connect the database in order to store users who work with the bot in it, as well as data on their interaction in the bot as part of the task that confronted him.The first thing I did in Google was an article from Habré with a translation of the mega-textbook Miguel Grinberg (the original of his blog is here ).The fourth chapter deals with connecting to a database using Flask and SQLAlchemy ORM. Then I thought: “Wow, how cool, now the problem is solved.” Oddly enough, but the file structure that the author suggested did not work for me.I did by analogy like his:microblog\
venv\
app\
__init__.py
models.py
main.py
In main.py, I do all the main work with the Telegram API.In app \ models.py, I create classes for the database.In app \ __ init__.py, everything was done as Miguel Grinberg wrote.But for some reason, in main.py, they didn’t want to pull me from app import db, app. Spent about an hour searching for problems, solutions on the Internet. As a result, Oleg Molchanov came across a YouTube channel and his video “Creating a Blog on Flask (lessons) - Creating Posts (Models) and SQLAlchemy”. There I watched how he makes a connection to the database and tried to go this way (without putting the models.py file into the app directory, without creating __init__.py.In general, now the structure of my project is simple to disgrace (and a little ugly, which confuses me, maybe in the future I’ll understand how to improve the structure):
As you can see, I have app.py, models.py, main.py, app.sqlite (the rest of the files do not relate to the current topic).In app.py I have this code:from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from config import Config
app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)
In models.py:from datetime import datetime
from app import db
class Users(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(120), index=True, unique=True)
last_name = db.Column(db.String(128))
first_name = db.Column(db.String(128))
created = db.Column(db.DateTime, default=datetime.now())
tasks = db.relationship('Tasks', backref='tasks')
def __init__(self, username, last_name, first_name):
self.username = username
self.last_name = last_name
self.first_name = first_name
def __repr__(self):
return '<User {}>'.format(self.username)
class Tasks(db.Model):
__tablename__ = 'tasks'
id = db.Column(db.Integer, primary_key=True)
owner_id = db.Column(db.Integer(), db.ForeignKey('users.id'))
name = db.Column(db.String(120), index=True)
start = db.Column(db.Boolean, default=False)
finish = db.Column(db.Boolean, default=False)
created_on = db.Column(db.DateTime, default=datetime.now())
updated_on = db.Column(db.DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow)
def __init__(self, *args, **kwargs):
super(Tasks, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Tasks {}>'.format(self.name)
In config.py:import os
from dotenv import load_dotenv
load_dotenv()
basedir = os.path.abspath(os.path.dirname(__file__))
class Config(object):
DEBUG = True
SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
SQLALCHEMY_DATABASE_URI = (os.environ.get('DATABASE_URL') or
'sqlite:///' + os.path.join(basedir, 'app.sqlite')) + '?check_same_thread=False'
SQLALCHEMY_TRACK_MODIFICATIONS = False
As you already understood, from the settings I removed the direct values ​​for the connection and rendered them in .env so that they would not shine on the prod or in SVN.First, app.db (where app.sqlite is now) was written to me in SQLALCHEMY_DATABASE_URI as the database. Yes, yes, and wrote down, as indicated in the instructions Miguel Grinberg. I hope you understand that you need to replace it with .sqlite. Before that, I thought of an hour after painful attempts to run the code.You can create databases and tables in sqlite by going to the debugging console (for example, in PyCharm I have Tools -> Python or Debug Console:
Here, we first connect the method we need in the desired file (I have it from models import db), and after successful connection, specify the db.create_all () command. After that, a database with all the necessary tables will be created. It’s worth knowing that if I make from app import db and run the db.create_all () command, the database file seems to be created, but it will be some kind of nonsense, not the database (I didn’t understand why).When I solved this problem, I thought again that now there are no difficulties left. It remains only to write a function for main.py, which, under certain events, will write data from the Telegram to the database. Well, I connected from models import Users and in the right place called the function:try:
find_user_in_db(username, first_name, last_name)
except NameError:
print("user ")
except:
print("An exception occurred")
def add_users_to_db(username, last_name, first_name):
"""
:return:
"""
data = Users(username, last_name, first_name)
db.session.add(data)
db.session.commit()
def find_user_in_db(username, first_name, last_name):
"""
:param username:
:param first_name:
:param last_name:
:return:
"""
user = db.session.query(Users).filter(Users.username == f'{username}').all()
if not user:
add_users_to_db(username, first_name, last_name)
In the last function, first instead of user = db.session.query (Users) .filter (Users.username == f '{username}'). First_or_404 () stood Users.query.filter_by (username = '{username}'). first_or_404 (). Here I also spent about half an hour to understand that this query in the database does not work and does not receive any data, so the query to the database is not sent. Google prompted one little article in which people said that it is better to use db.session.query (Users). Why so, I xs, did not waste time analyzing. After that, the data began to be written to the database.This concludes this article because I described what I wanted to describe and solved the problem of connecting the database to Flask.This article is written only for the reason that we “June” love when reading instructions. I myself also tried to find instructions for connecting the database to Flask. I did not find any complete instructions, so I had to make my way through the obstacles. Well, I decided to describe my experience as the next one who will look for ready-made instructions.PS Who is looking for a directly ready-made solution, then I ask to the repository on GitHubPSS I will be glad if someone does a code review and gives their recommendations for improvement.Thank you for the attention.