Chapter 5 of "Introduction to Flask": learning Flask Sqlalchemy is enough. Don't write too many

According to my personal understanding, database is a tool to store data and the direct relationship between data and data. No matter the complexity or size of the data it stores, it has no essential difference from a tidy room. The database can be easily obtained and modified after authentication, and the speed is also critical. Today, I'll talk about how to use flash Sqlalchemy to use mysql gracefully in flash

Install Mysql (some skip directly)

Installation environment is really a pain. I recommend using pagoda( bt.cn )It is also convenient to install python, php, nginx and other websites

Just skip the download and configure the panel directly (pagoda is a server management tool based on the web, which is suitable for windows and linux)

If your host is on the intranet (without public ip), change the domain name to "127.0.0.1" or "localhost". Otherwise, you cannot access it. Confirm that the panel is running. Click "open panel"“

Enter the default account in the panel

Click "database". The first time you use the panel, you will be prompted to install the database. Just follow the steps to install. After the installation, return to the database interface. Then create a new database and Ok

Example

from flask import *
from flask_sqlalchemy import SQLAlchemy

# Set the flag object

app = Flask(__name__)


# Set whether to record modification transactions of database
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# Whether to display the original statement during database operation
app.config['SQLALCHEMY_ECHO'] = True


# Set database configuration object


class Configs():
    def __init__(self):
        # This is the host address of the database. The intranet is usually localhost or 127.0.0.1
        self.host = 'localhost'
        # This is the direct port for database operation, generally 3306
        self.port = '3306'
        # This is the database name, just filled in on the pagoda panel,
        self.database_name = 'csdn'
        # This is the database user name. It is the user name just filled in the pagoda panel. It is generally the same as the database name
        self.database_user = 'csdn'
        # This is the user password of the database. For the password just set in the pagoda panel, it is recommended to save it with symmetric encryption in the regular project
        self.database_password = 'pcPTpJMWLSHnGtcn'
        # This is the connection address on the database
        self.database_url = "mysql+pymysql://{database_user}:{database_password}@{host}:{port}/{database_name}?charset=utf8".format(database_user=self.database_user, database_password=self.database_password, host=self.host, port=self.port, database_name=self.database_name)


# Configure database address
app.config['SQLALCHEMY_DATABASE_URI'] = Configs().database_url

# Elegant database objects
db_obj = SQLAlchemy(app)
db_obj.init_app(app)

# Define the first model


class Text(db_obj.Model):
    # Set the table name. If it is not defined, it defaults to the class name
    __tablename__ = "Text"

    # db.Column Is the definition column (that is, the properties of each model in the table), primary_ The key attribute is to set the location key, such as id
    id = db_obj.Column(db_obj.Integer, primary_key=True)
    text = db_obj.Column(db_obj.String(72))
    


# Add all tables to the database
db_obj.create_all()

# Try to add one or two data
test_text1 = Text(text='I'm a new Blogger')
test_text2 = Text(text='Let me have a little star')

# Elegant data submission
db_obj.session.add(test_text1)
db_obj.session.add(test_text2)
db_obj.session.commit()

Alas, there are still too many pits. This example has been written for half an hour

Operation results

2020-06-13 22:21:58,034 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2020-06-13 22:21:58,036 INFO sqlalchemy.engine.base.Engine {}
2020-06-13 22:21:58,041 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2020-06-13 22:21:58,042 INFO sqlalchemy.engine.base.Engine {}
2020-06-13 22:21:58,051 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2020-06-13 22:21:58,054 INFO sqlalchemy.engine.base.Engine {}
2020-06-13 22:21:58,060 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2020-06-13 22:21:58,062 INFO sqlalchemy.engine.base.Engine {}
2020-06-13 22:21:58,071 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2020-06-13 22:21:58,072 INFO sqlalchemy.engine.base.Engine {}
2020-06-13 22:21:58,078 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2020-06-13 22:21:58,080 INFO sqlalchemy.engine.base.Engine {}
2020-06-13 22:21:58,085 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2020-06-13 22:21:58,086 INFO sqlalchemy.engine.base.Engine {}
2020-06-13 22:21:58,094 INFO sqlalchemy.engine.base.Engine DESCRIBE `Text`
2020-06-13 22:21:58,096 INFO sqlalchemy.engine.base.Engine {}
2020-06-13 22:21:58,125 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-06-13 22:21:58,129 INFO sqlalchemy.engine.base.Engine INSERT INTO `Text` (text) VALUES (%(text)s)
2020-06-13 22:21:58,135 INFO sqlalchemy.engine.base.Engine {'text': 'I'm a new Blogger'}
2020-06-13 22:21:58,139 INFO sqlalchemy.engine.base.Engine INSERT INTO `Text` (text) VALUES (%(text)s)
2020-06-13 22:21:58,141 INFO sqlalchemy.engine.base.Engine {'text': 'Let me have a little star'}
2020-06-13 22:21:58,152 INFO sqlalchemy.engine.base.Engine COMMIT

Take a look at the results in the pagoda

phpmyadmin is used for viewing, which can be installed in "software management" of pagoda panel

There are detailed comments in the example, you can do it by hand

data type

Basic data types

data type	Description / for py data structure
Integer	Int (int)
String	String (str)
Text	Text (str)
DateTime	Date( time.time)
Float	Floating point (decimal)
Boolean	Boolean (True or False)
LargeBinary	Very long binary( str.code('utf-8 '), usually a document, such as a picture)
PickleType	Storing objects in python

Picketype is to store objects in Python in files, including dictionaries, objects, and functions, which can be serialized. Please refer to this article for details https://blog.csdn.net/chunmi6974/article/details/78392230

Upgrade the CollectsText sample

This is an example in Chapter 2. Let's change the file storage to the database storage

effect

What about the database

Source code

from flask import *
from flask_sqlalchemy import SQLAlchemy

# Set the flag object

app = Flask(__name__)


# Set whether to record modification transactions of database
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# Whether to display the original statement during database operation
app.config['SQLALCHEMY_ECHO'] = True


# Set database configuration object


class Configs():
    def __init__(self):
        # This is the host address of the database. The intranet is usually localhost or 127.0.0.1
        self.host = 'localhost'
        # This is the direct port for database operation, generally 3306
        self.port = '3306'
        # This is the database name, just filled in on the pagoda panel,
        self.database_name = 'csdn'
        # This is the database user name. It is the user name just filled in the pagoda panel. It is generally the same as the database name
        self.database_user = 'csdn'
        # This is the user password of the database. For the password just set in the pagoda panel, it is recommended to save it with symmetric encryption in the regular project
        self.database_password = 'pcPTpJMWLSHnGtcn'
        # This is the connection address on the database
        self.database_url = "mysql+pymysql://{database_user}:{database_password}@{host}:{port}/{database_name}?charset=utf8".format(database_user=self.database_user, database_password=self.database_password, host=self.host, port=self.port, database_name=self.database_name)


# Configure database address
app.config['SQLALCHEMY_DATABASE_URI'] = Configs().database_url

# Elegant database objects
db_obj = SQLAlchemy(app)
db_obj.init_app(app)

# Define the first model


class Text(db_obj.Model):
    # Set the table name. If it is not defined, it defaults to the class name
    __tablename__ = "Text"

    # db.Column Is the definition column (that is, the properties of each model in the table), primary_ The key property is to set the location key, such as id
    id = db_obj.Column(db_obj.Integer, primary_key=True)
    text = db_obj.Column(db_obj.String(72))
    


# Add all tables to the database
db_obj.create_all()

def get_all_text():
    a = list()
    for i in range(1,Text.query.count()):
        b = Text.query.get(i)
        a.append(b.text)
        
    return a

@app.route('/')
def form():
	return render_template('index.html',text=get_all_text())

@app.route('/post',methods=['POST'])
def post():
    new = Text(text=request.form['text'])
    db_obj.session.add(new)
    db_obj.session.commit()
    return "ok"


app.run()

end

Ah, I've finished. This article mainly talks about some basic knowledge about flash SQL alchemy, and of course, the relational and advanced query methods. I'm ashamed of that. The sample programs in this article are all original and can be run. If there are some mistakes in the article, please put forward suggestions. Of course, if you think the article is good, please like it. qwq