sqlalchemy常用语法

转自https://www.cnblogs.com/bigberg/p/8318214.html
一、新增

# 新增一条数据
user_obj = User(name="bigberg", passwd="twgdh123")
Session.add(user_obj)
Session.commit()
 
# 新增多条数据
user_obj1 = User(name="bigberg", passwd="twgdh123")
user_obj2 = User(name="someone", passwd="twgdh123")
Session.add_all([user_obj1,user_obj2])
 
Session.commit()

二、查询
2.1普通查询

# filter_by获取的是对象列表
data = Session.query(User).filter_by(name='bigberg').all()
print(data)
print(data[0].id, data[0].name, data[0].passwd)
 
#输出
[<__main__.User object at 0x0000029DC2D51160>]
1 bigberg twgdh123
 
 
# 不指定条件
data = Session.query(User).filter_by().all()
print(data)
print(data[0].id, data[0].name, data[0].passwd)
 
# 输出
[<__main__.User object at 0x0000026C9D27F0F0>, <__main__.User object at 0x0000026C9D27F160>, <__main__.User object at 0x0000026C9D27F1D0>]
1 bigberg twgdh123

2.2 查询数据显性展示

class User(Base):
    __tablename__ = "user"  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    passwd = Column(String(64))
 
    def __repr__(self):
        return "id:%s name:%s password:%s" % (self.id, self.name, self.passwd)　
        
data = Session.query(User).filter_by().all()
print(data)
print(data[0].id, data[0].name, data[0].passwd)
 
#输出
[id:1 name:bigberg password:twgdh123, id:2 name:Jerry password:twgdh123, id:3 name:Jack password:twgdh123]
1 bigberg twgdh123

2.3 获取第一条数据

data = Session.query(User).filter_by().first()
print(data)
print(data.id, data.name, data.passwd)
 
# 输出
id:1 name:bigberg password:twgdh123
1 bigberg twgdh123

2.4 获取所有数据

print(Session.query(User.id, User.name, User.passwd).all())
 
#输出
[(1, 'bigberg', 'twgdh123'), (2, 'Jerry', 'twgdh123'), (3, 'Jack', 'twgdh123')]

2.5 多条件查询

data = Session.query(User).filter(User.id > 2).filter(User.id < 7).all()
print(data)
 
#输出
[id:3 name:Jack password:twgdh123]

2.6 模糊查询

data = Session.query(User).filter(User.name.like('J%')).all()
 
#输出
[id:2 name:Jerry password:twgdh123, id:3 name:Jack password:twgdh123]

2.7 and / or

from sqlalchemy import and_, or_
 
data = Session.query(User).filter(and_(User.id > 2, User.name.like('J%'))).all()
print(data)
 
#输出
[id:3 name:Jack password:twgdh123]

2.8 in_

data = Session.query(User).filter(User.id.in_([1,3])).all()
print(data)
 
 
data = Session.query(User).filter(User.name.in_(['bigberg', 'Jack'])).all()
print(data)

2.9 排序

data = Session.query(User).order_by(User.name.desc()).all()
print(data)

3.0联合查询

#方法1
data = session.query(Users, Favor).filter(User.id == Favor.nid).all()
#方法2
data = session.query(Person).join(Favor).all()  # 需要表存在外键关联
#方法3
data = session.query(Person).join(Favor, isouter=True).all()  # 需要表存在外键关联
print(data)

三、修改数据
直接赋值

# data = Session.query(User).filter(User.name=='Marry').first()
data = Session.query(User).filter_by(name='Marry').first()
data.name = 'Tom'
Session.commit()

update

Session.query(User).filter_by(name='Tom').update({'name': 'Hary'})
Session.commit()

回滚

Session.query(User).filter_by(name='Hary').update({'name': 'John'})
print(Session.query(User).filter_by(name='John').all())
# 回滚
Session.rollback()
print(Session.query(User).filter_by(name='John').all())
Session.commit()
 
#输出
[id:2 name:John password:twgdh123]
[]
 
mysql> select * from user;
+----+---------+----------+
| id | name    | passwd   |
+----+---------+----------+
|  1 | bigberg | twgdh123 |
|  2 | Hary    | twgdh123 |
|  3 | Jack    | twgdh123 |
+----+---------+----------+
3 rows in set (0.00 sec)
 
# Hary 确实没有改成 John

四、统计

data = Session.query(User).filter(User.name.like('%a%')).count()
print(data)
 
#输出
2

五、分组

from sqlalchemy import func
 
data = Session.query(User.name, func.count(User.name)).group_by(User.name).all()
print(data)
 
# 输出
[('bigberg', 1), ('Hary', 1), ('Jack', 1)]

六、主键、索引、唯一约束、联合唯一约束、联合主键
转自：https://blog.csdn.net/weixin_42902669/article/details/102666970

1. 主键

from sqlalchemy import *

test = Table(
	'test', metadata,
	Column('id', BigInteger, primary_key=True)
)

2. 索引

from sqlalchemy import *

test = Table(
	'test', metadata,
	Column('id', BigInteger, index=True)
)

3. 唯一约束

from sqlalchemy import *

test = Table(
	'test', metadata,
	Column('id', BigInteger, unique=True)
)

4. 联合唯一约束

from sqlalchemy import *

test = Table(
	'test', metadata,
	Column('id', BigInteger, primary=True),
	Column('col1', String(20)),
	Column('col2', Numeric(20, 4)),
	UniqueConstraint('col1', 'col2', name='idx_col1_col2')
)

5. 联合主键约束
   方法一:

from sqlalchemy import *

test = Table(
	'test', metadata,
	Column('id', BigInteger),
	Column('col1', String(20)),
	PrimaryKeyConstraint('id', 'col1', name='idx_id_col1')
)

方法二:

from sqlalchemy import *

test = Table(
	'test', metadata,
	Column('id', BigInteger, primary_key=True),
	Column('col1', String(20), primary_key=True),
)