sqlalchemy 数据库操作

sqlAlchemy是python中最著名的ORM(Object Relationship Mapping)框架。

ORM 从开发角度说,就是采用面向对象的方式来直接操作数据库,方便的很,不用写那么多数据库查询语句。

这里记录一下增删查改命令。

安装方法:

1
2
python -m pip install sqlalchemy
python -m pip install pymysql

参考代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
from sqlalchemy import Column,String,Integer,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('mysql+pymysql://root:root@localhost:3306/info')

DBSession = sessionmaker(bind=engine)


class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
passwd = Column(String)

# 增加数据
try:
db = DBSession()
db.add(User(name="Hacker1",passwd="1234561"))# 增
db.commit()
db.close()
except :
pass

## 查询数据
try:
db = DBSession()
# 查所有
results = db.query(User).all()
for r in results:
print(r.name)
#条件过滤
result = db.query(User).filter_by(id = 4).first()
print(result.name)
# 条件大于
results = db.query(User).filter(User.id > 5).all()
for r in results:
print(r.name)
db.close()
except :
pass

## 修改数据
try:
db = DBSession()
result = db.query(User).filter_by(id = 7).first()
result.name = "what"
db.commit()
db.close()
except :
pass

## 删
try:
db = DBSession()
result = db.query(User).filter_by(id = 9).first()
db.delete()
db.commit()
db.close()
except Exception as e :
print(e)
pass