# Python-SQLalchemy

**Repository Path**: pyaizx/Python-SQLalchemy

## Basic Information

- **Project Name**: Python-SQLalchemy
- **Description**: SQLalchemy基础
- **Primary Language**: Python
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 2
- **Created**: 2020-08-11
- **Last Updated**: 2021-04-12

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## Python-SQLAlchemy

### 1.简介

​	SQLAlchemy“采用简单的Python语言，为高效和高性能的数据库访问设计，实现了完整的企业级持久模型”。SQLAlchemy的理念是，[SQL数据库](https://baike.baidu.com/item/SQL数据库/2081086)的量级和性能重要于对象集合；而对象集合的抽象又重要于表和行。因此，SQLAlchemy采用了类似于[Java](https://baike.baidu.com/item/Java/85979)里[Hibernate](https://baike.baidu.com/item/Hibernate/206989)的数据映射模型，而不是其他ORM框架采用的Active Record模型。不过，Elixir和declarative等可选插件可以让用户使用声明语法

### 2.基本使用

```python
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Enum

# 连接mysql
db_connect_string = '{database_type}+{driver}://{username}:{password}@{host}:{port}/{db}'.format(
    database_type='mysql', driver='pymysql', username='root', password='python123',
    host='127.0.0.1', port=3306, db='sqlalchemy_01'
)

# 创建引擎
engine = create_engine(db_connect_string)

# 创建orm对象
Base_Models = declarative_base()


# 创建orm模型
class User(Base_Models):
    # 表名
    __tablename__ = 'user'
    # id  primary_key 主键 autoincrement 是否自增
    id = Column(Integer, primary_key=True, autoincrement=True)
    # name  32 个字段
    name = Column(String(32))
    # 性别
    gender = Column(Enum('男', '女'))

    __table_args__ = (

    )


def init_db():
    """
    初始化数据库
    """
    Base_Models.metadata.create_all(engine)


def drop_db():
    """
    删除数据库
    """
    Base_Models.metadata.drop_all(engine)


init_db()
```

### 3.增删改查

```python
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Enum
from sqlalchemy.orm.session import sessionmaker


# 连接mysql
db_connect_string = '{database_type}+{driver}://{username}:{password}@{host}:{port}/{db}'.format(
    database_type='mysql', driver='pymysql', username='root', password='python123',
    host='127.0.0.1', port=3306, db='sqlalchemy_01'
)

# 创建引擎
engine = create_engine(db_connect_string)


# 创建orm对象
Base_Models = declarative_base()


# 创建orm模型
class User(Base_Models):
    # 表名
    __tablename__ = 'user'
    # id  primary_key 主键 autoincrement 是否自增
    id = Column(Integer, primary_key=True, autoincrement=True)
    # name  32 个字段
    name = Column(String(32))
    # 性别
    gender = Column(Enum('男', '女'))


def init_db():
    """
    初始化数据库
    """
    Base_Models.metadata.create_all(engine)


def drop_db():
    """
    删除数据库
    """
    Base_Models.metadata.drop_all(engine)


# 创建session 对象
Session = sessionmaker(bind=engine)
session = Session()

obj1 = User(name='python', gender='男')


'------------      增加        -------------'
# 添加到数据库中
# session.add(obj1)

# 添加多个值
user_list = [
    User(name='python', gender='男'),
    User(name='java', gender='女'),
    User(name='golang', gender='男')
]
session.add_all(user_list)

session.commit()
session.close()


'-------------      查      --------------'
# 取全部
user = session.query(User).all()
for us in user:
    print(us.id, us.name, us.gender)

# 取第一个值
user = session.query(User.name, User.gender).first()
print(user)

# 条件查询   user.id 大于 2的数据
user = session.query(User).filter(User.id > 2)
for us in user:
    print(us.id, us.name, us.gender)

'-------------- 删 ------------------'
# 先查后删
user = session.query(User).filter(User.id > 2).delete()
print(user)

'--------------- 修改  -------------------'
user = session.query(User).filter(User.id == 2).update({'name': 'java'})
session.commit()
session.close()
```

### 4.多对一

**config.py**

```python
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker


# 连接mysql
db_connect_string = '{database_type}+{driver}://{username}:{password}@{host}:{port}/{db}'.format(
    database_type='mysql', driver='pymysql', username='root', password='python123',
    host='127.0.0.1', port=3306, db='sqlalchemy_01'
)

# 创建引擎
engine = create_engine(db_connect_string)


# 创建orm对象
Base_Models = declarative_base()


# 创建session 对象
Session = sessionmaker(bind=engine)
session = Session()


def init_db():
    """
    初始化数据库
    """
    Base_Models.metadata.create_all(engine)


def drop_db():
    """
    删除数据库
    """
    Base_Models.metadata.drop_all(engine)
```

**test_1.py**

```python
from config import session, Base_Models, drop_db, init_db
from sqlalchemy import Column, String, Integer, Enum, ForeignKey
from sqlalchemy.orm import relationship


class UserType(Base_Models):
    # 用户类型
    __tablename__ = 'usertype'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))
    # 存的是用户 user 通过 user_type 访问 usertype
    user_id = relationship('User', backref='user_type')


# 创建orm模型
class User(Base_Models):
    # 表名
    __tablename__ = 'user'
    # id  primary_key 主键 autoincrement 是否自增
    id = Column(Integer, primary_key=True, autoincrement=True)
    # name  32 个字段
    name = Column(String(32))
    # 性别
    gender = Column(Enum('男', '女'))
    # 用户类型
    user_type_id= Column(Integer, ForeignKey('usertype.id'))


user_type_list = [
    UserType(name='普通用户'),
    UserType(name='白金用户'),
    UserType(name='钻石用户'),
    UserType(name='黑金用户')
]


session.add_all(user_type_list)
session.commit()
session.close()

utype = session.query(UserType).all()

for u in utype:
    print(u.id, u.name)


# 添加
user = User(name='Python', gender='男', user_type_id=3)

session.add(user)
session.commit()
session.close()

# 查询  多方访问少
user = session.query(User).all()

for us in user:
    print(us.user_type.name)
```