Flask – SQLAlchemy Operations (Basic)

The following covers the basic query statements of SQLAlchemy, and the next article covers its advanced queries (aggregate, self-association, join, subquery, and so on)

Model class

Class User(db.model): class User(db.model) __tablename__ = 'user' uid = db.Column(db.String(32), primary_key=True, nullable=False) username = db.Column(db.String(20), nullable=True) password = db.Column(db.String(128), nullable=True) email = db.Column(db.String(30), nullable=True) addresses = db.relationship('Address', Backref ='user') # class Address(db.model): __tablename__ = 'address' aid = db.Column(db.String(32), primary_key=True, nullable=False) name = db.Column(db.String(32), nullable=True) site = db.Column(db.String(100), nullable=True) phone = db.Column(db.Integer, nullable=True) uid = db.Column(db.String(32), Db.foreignKey ('user.uid')) # Table orderItem = db.table (' orderItem ', db.column ('gid', db.string (32), nullable=True), db.Column('product_id', db.String(32), db.ForeignKey('product.pid')), db.Column('order_id', db.String(32), Db.foreignKey ('order.oid'))) # class Product(db.model): __tablename__ = 'product' pid = db.Column(db.String(32), nullable=False, primary_key=True) pname = db.Column(db.String(50), nullable=True) market_price = db.Column(db.Float, nullable=True) shop_price = db.Column(db.Float, nullable=True) pimage = db.Column(db.String(200), nullable=True) pdate = db.Column(db.Date, nullable=True) is_hot = db.Column(db.Integer, nullable=True) pdesc = db.Column(db.String(255), nullable=True) pflag = db.Column(db.Integer, Nullable =True) order = db.relationship(' order ', secondary=OrderItem) __tablename__ = 'order' oid = db.Column(db.String(32), nullable=False, primary_key=True) count = db.Column(db.Integer, nullable=True) subtotal = db.Column(db.Float, nullable=True) ordertime = db.Column(db.DateTime, nullable=True) flag = db.Column(db.String(10), nullable=True)

increase

One to one

order = models.Orders(oid=orderid, ordertime=datetime.now(), total=pcount, Uid =pid) # build object models.db.session.add(order) # add object models.db.session.mit () # commit transaction

More than a pair of

p = models.User(uid='122', username='hello', password='123456', Address(aid='1111111111',site=' XXXXXXXXXX ') # Child c2 = models.Address(aid='1111111111',site=' XXXXXXXXXX ') # Child c2 = Model. Address(aid='22222222 ', site='yyyyyyyyy ') C2] # add models.db.session.add(p) # add models.db.session.mit () # commit

Many to many

P = models.product (pid='1',pname='hello') # o = models.order (oid='1'), Models.db.session.add (p) # add models.db.session.mit () # commit

check

One to one

Models.product.query.get (pid) # Returns an object based on the primary key, Have to query the primary key models. The Product. The query. All # () returns all the object models. Product. Query. Filter_by pid = (pid). First # () returns the first object, Retrieve any value models. The Product. Query. Filter_by (cid = '1'). The limit (4) all () # limit return objects

More than a pair of

P = models.user.query.get(uid) p = models.user.query.get(uid) U = models.address.query.get (1) print(u.u.ser) = "models.address.query.get (1) print(u.u.ser)" = "models.address.query.get (1) print(u.u.ser)"

Many to many

Print (p = models.product.query.get (1) print(p = models.order.query.get (1) print(p = models.product.query.get (1) print(p = models.product.query.

change

One to one

Good = models. The Product. The query. Filter_by (pid = pid). The first (#) to obtain good. Pflag = 6 # modify models.db.session.com MIT (#) to submit

More than a pair of

u = models.User.query.get(1)
    for i in u.addresses:
        i.name = 'Gage'
models.db.session.commit()

Many to many

o = models.Order.query.get(1)
    for i in o.product:
        i.pname = 'Gage'
models.db.session.commit()

delete

One to one

Add = models. Address. Query. Filter_by (aid = aid). The first (#) to obtain models. The session. The delete (add) # add models.db.session.com MIT () # submit

More than a pair of

# select * from user, delete from user, delete from user, delete from user; Actual development need to be filtered in using filter_by or filter for specific delete u = models. The User. The query. The get (1) for I in u.a ddresses: Print (i.id) models.db.session. Delete (I) models.db.session.mit () # print(i.id) models.address.query.get (1) models.db.session.delete(a.user) models.db.session.commit()

Many to many

# The same goes for actual development, P = models.product.query.get (1) o = models.order.query.get (1) p.order.remove(o) p = models.product.query.get (1) p.order.remove(o) P = models.product.query.get (1) o = models.order.query.get (1) o.product.remove(p) models.db.session.commit()