SQLAlchemy ORM
最后更新于：2022-04-02 02:17:57
[TOC]
> [概述](https://www.cnblogs.com/coder2012/p/4741081.html)
## 概述
支持 mysql mssql oracle sqlite
## SQLAlchemy
`pip install SQLAlchemy`
>window 需要安装 `pip install mysqlclient`
### 连接
```
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('mysql+mysqldb://root:root@localhost:3306/blog')
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
username = Column(String(64), nullable=False, index=True)
password = Column(String(64), nullable=False)
email = Column(String(64), nullable=False, index=True)
def __repr__(self):
return '%s(%r)' % (self.__class__.__name__, self.username)
Base.metadata.create_all(engine)
```
1. 一对多关系
方法一
```python
class User(Base):
articles = relationship('Article')
class Article(Base):
user_id = Column(Integer, ForeignKey('users.id')) #多的一方用外键
author = relationship('User')
```
方法二
```python
class User(Base):
__tablename__='users'
articles = relationship('Article', backref='user')
class Article(Base):
__tablename__='articles'
user_id = Column(Integer, ForeignKey('users.id'))
```
*对relationship('Article', backref='user')注解*
`Article`表示对应`Article`类,`user`作用是在插入数据时,`user_id`用`user`代替如下
```python
article = Article(
title=faker.sentence(),
content=' '.join(faker.sentences(nb=random.randint(10, 20))),
user=random.choice(faker_users), #此处的user既是backref的值
category=random.choice(faker_categories)
)
```
2. 一对一
```
class User(Base):
userinfo = relationship('UserInfo', backref='user', uselist=False)
class Article(Base):
user_id = Column(Integer, ForeignKey('users.id'))
```
### 操作数据
```
Session = sessionmaker(bind=engine)
session = Session()
#添加
data=1235
#方式一
user = HsUser(USER_ID=data,USER_NAME=data,USER_LOGIN=data)
//方式二
# user.USER_ID=data
# user.USER_NAME=data
# user.USER_LOGIN=data
#添加person对象，但是仍然没有commit到数据库
session.add()
#commit操作
session.commit()
# 查询
UserOne = session.query(User).get(1)
print(UserOne.username);
# 条件查询
UserOne = session.query(User).filter_by(username='Scott Black').first()
print(UserOne.username);
# 查询多个
UserOne = session.query(User).filter_by(username='Scott Black').all()
print(UserOne);
# 更新
a = session.query(Article).get(10)
a.title = 'My test blog post'
session.add(a)
session.commit()
# 删除
a = session.query(Article).get(10)
session.delete(a)
session.commit()
```
';