【Flask】Sqlalchemy 增删该查操作

 1 # coding:utf-8
 2 # Author: liangjun.
 3 
 4 from sqlalchemy import create_engine, Column, Integer, String
 5 from sqlalchemy.ext.declarative import declarative_base
 6 from sqlalchemy.orm import sessionmaker
 7 
 8 HOSTNAME = '127.0.0.1'
 9 PORT = 3306
10 DATABASE = 'sqlalchemy_first'
11 USERNAME = 'root'
12 PASSWORD = '123456'
13 
14 DB_URI = 'mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}?charset=utf8'.format(
15         username=USERNAME, password=PASSWORD, host=HOSTNAME, port=PORT, dbname=DATABASE
16 )
17 engine = create_engine(DB_URI)
18 Base = declarative_base(engine)
19 Session = sessionmaker(engine)
20 session = Session()
21 
22 
23 # 创建ORM模型
24 class Person(Base):
25     __tablename__ = 'person'
26     id = Column(Integer, primary_key=True, autoincrement=True)
27     name = Column(String(length=50))
28     age = Column(Integer)
29 
30     def __repr__(self):
31         return "Person<name:{}, age{}>".format(self.name, self.age)
32 
33 
34 def add_data():
35     p1 = Person(name='saber', age=18)
36     p2 = Person(name='slamdunk', age=18)
37     # 添加一条
38     session.add(p1)
39     # 添加多条记录
40     session.add(p1, p2)
41     session.commit()
42 
43 
44 def update_data():
45     person = session.query(Person).first()
46     person.name = 'saber007'
47     session.commit()
48 
49 
50 def search_data():
51     # 查找所有
52     persons_1 = session.query(Person).all()
53     for person in persons_1:
54         print person
55 
56     # filter_by
57     persons_2 = session.query(Person).filter_by(name='saber').all()
58     for _p in persons_2:
59         print _p
60 
61     # filter
62     persons_3 = session.query(Person).filter(Person.name == 'saber').all()
63     for _p in persons_3:
64         print _p
65 
66     # get
67     # 根据主键查找, 如没有则返回None
68     person = session.query(Person).get(1)
69     print "---------------get------------------"
70     print person
71 
72     # first
73     # 获取结果集中的第一条
74     person = session.query(Person).first()
75     print person
76 
77 
78 def delete_data():
79     person = session.query(Person).first()
80     session.delete(person)
81     session.commit()
82 
83 if __name__ == "__main__":
84     # add_data()
85     # search_data()
86     # update_data()
87     delete_data()