III Flask database [flask Sqlalchemy]

I database

1. Foundation

1.1 download

Format: PIP install flask Sqlalchemy

1.2 integrated configuration

app.config['SQLALCHEMY_DATABASE_URI']='mysql://root:root@127.0.0.1/jack'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

Special: import pymysql is required to import mysql; pymysql. install_ as_ MySQLdb()

1.3 instantiation

db =SQLAlchemy(app)
class Node(db.Model):
    __tablename__ = 'lu' #Name of the form
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(64), unique=True)

2. Database operation

2.1 create form

Format: dB create_ all()

2.2 additions

Format:

content=Node(body='d')
db.session.add(content)
db.session.commit()

2.3 read content

Formats: models query. Filtering method Query method

2.3.1 filtering method

Specific:

== Node.query.filter(Node.id == 10).all()[0].body
like

a = Node.query.filter(Node.body.like('%534354%')).all()
for i in a:
    print(i.body)

in a = Node.query.filter(Node.id.in_([1,2])).all() # feedback incomplete data with id=1 and id=2
not in a = Node.query.filter(~Node.id.in_([1,2])).all()
and from sqlalchemy import and_;a = Node.query.filter(and_(Node.id==1,Node.body =='51')).all() #and juxtapose two conditions are true and return a result (simple can be separated by commas)
or from sqlalchemy import or_;a = Node.query.filter(or_(Node.id==1,Node.body =='51')).all()

2.3.2 query method

2.4 changes

Format:

node = Node.query.filter(Node.id == 5).first()
node.body='dbdbdbd'
db.session.commit()

Interpretation: obtained directly through; Then modify; Just submit

2.5 deletion

node = Node.query.filter(Node.id == 5).first()
db.session.delete(node)
db.session.commit()

Interpretation: obtained directly through; Then delete; Just submit

3. Database association operation

3.1 one to many

Principle: in fact, it is the association between forms. For example, the author is a form (belonging to one [refers to one record in the corresponding table]) and an article form (belonging to multiple [refers to multiple records in the corresponding table]). One to many means that you can directly access the content of the article form through the author form. The key is to set the same ID (or other same content) in the two tables and access through the same content.

establish:

class Author(db.Model):
     __tablename__ = 'author'
     id = db.Column(db.Integer, primary_key=True)
     name = db.Column(db.String(30))
     article = db.relationship('Article', backref='article_look')

class Article(db.Model):
     __tablename__ = 'article'
     id = db.Column(db.Integer, primary_key=True)
     title = db.Column(db.String(30))
     text  = db.Column(db.Text)
     article_id = db.Column(db.Integer, db.ForeignKey('author.id'))


db.create_all()

add to:

# add to
content = Author(name ='jack')
db.session.add(content)
db.session.commit()
content1 = Author(name ='luck')
db.session.add(content1)
db.session.commit()
for i in range(0,10):
     content2 = Article(title=f'ti{i}', text=f'daaaaaaaaaaaaa{i}',article_id=1)
     db.session.add(content2)
     db.session.commit()
     if i >=7:
          content3 = Article(title=f'ti{i}', text=f'daaaaaaaaaaaaa{i}', article_id=2)
          db.session.add(content3)
          db.session.commit()

Cross table query (one query for multiple):

print(Author.query.filter(Author.id == 1).first().article[0].text)
print(Author.query.filter(Author.id == 2).first().article[0].text)
# daaaaaaaaaaaaa0
# daaaaaaaaaaaaa7
# Principle: you can access the whole article table by specifying the value of relationship

Cross table query (one more query):

print(Article.query.filter(Article.article_id == 1).first().article_look.name)
print(Article.query.filter(Article.article_id == 2).first().article_look.name)
#jack
#luck
#Principle: through the backref relation function, on the other side of the author (the article side), use this parameter to backcheck

3.2 one to one

Principle: This is similar to one to many. For example, the relationship between user form and ID card form must be one to one
Implementation: Article = dB relationship('Article', uselist=False, backref='article_look')
Explanation: change to uselist=False. The table returns only one content. Other operations are the same

3.3 many to many

Principle: This is similar to one to many but very different. The query method is the same. It is used to solve problems such as paying attention to and being paid attention to.

Difference: one to many (many tables have duplicate IDS), but many to many two tables may not have duplicate IDs. In order to contact, the intermediary table, that is, the table with duplicate relationship id, is used, in which the intermediary table passes dB Create a table and contact the mediation table through secondary