SQLAlchemy创建表及增删改查

1 SQLAlchemy创建数据表

# 1. 导入SQLAlchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
 
# 2.ORM 模型基类
Base = declarative_base() # 相当于Django model
 
# 3. 创建ORM对象
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), index=True)
 
# 4.创建数据库连接
 
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:root@127.0.0.1:3306/my_sqlalchemy?charset=utf8')
 
# 5. engine数据库中创建所有继承Base类的ORM对象
Base.metadata.create_all(engine)

 2 SQLAlchemy增删改查

def loadSession():
    Session = sessionmaker(bind=engine)
    session = Session()
    return session


#获取数据库会话
session = loadSession()

#增加
u1 = User(id=1,userName="Rose",password="aaaa",gender="F")
u2 = User(id=2, userName="Joe", password="bbbb",gender="M")
session.add(u1)
session.add(u2)
session.commit()

#删除
session.query(User).filter(User.id > 2, User.gender == None).delete()
session.commit()

#修改
session.query(User).filter(User.userName == "jack").update({User.password:"xxxx"})
session.commit()

#查询
    #查第一行
session.query(User.id,User.userName,User.password).first()
    #查所有行
session.query(User.id, User.userName, User.password).all()
    #根据id倒序并取前两行
session.query(User).order_by(User.id.desc()).limit(2)


# 新增多条数据
def add_more_user():
    session = DBSession()
    session.add_all([
        User(name='guanyu', age=4, password='11111'),
        User(name='zhangfei', password='2233'),
        User(name='zhenji', password='44556')
    ])
    session.commit()
    session.close()


# 新增数据含中文,只要用unicode的字符即可
def add_user_for_zh():
    session = DBSession()
    new_user = User(name=u'关羽', password='12322233')
    session.add(new_user)
    session.commit()
    session.close()

# 查询
def query_user():
    session = DBSession()
    q_user = session.query(User).filter(User.id == 4).one()  # one表示结果有且仅有一个
    print('name', q_user.name)
    session.close()  # 查询不用commit，如果不commit，会自动执行rollback

# 删除
def delete_user():
    session = DBSession()
    deleted_num = session.query(User).filter(User.id > 3).delete()
    print(deleted_num)
    session.commit()
    session.close()

def insert_update(all_needed_data_lists):
  # 是需要插入数据库的数据 格式[{key: value, ... }, { }, { }...]
  for item in all_needed_data_lists:
    try:
      # 利用exists判断目标对象是否存在，返回True或Faults
      it_exists = session.query(
          exists().where(ServiceOrder.serviceOrderId == item['serviceOrderId'] )
        ).scalar()
    except Exception as e:
      self.log.error(e)
      break
    try:
      # 如果不存在，进行新增；存在的话就更新现存的数据
      if not it_exists:
        session.add(ServiceOrderRow)
      else:
        session.query(ServiceOrder).filter(ServiceOrder.serviceOrderId == item['serviceOrderId'])\
          .update(item)
    except Exception as e:
      self.log.error(e)
      break
  try:
    session.commit()
    self.log.info('数据更新成功！')
  except:
    session.rollback()
    self.log.info('数据更新失败！')
    

3 动态创建表

def get_model(suffix):
    class BaseModel(Base):
        __tablename__ = f'table_{suffix}'
        __table_args__ = {'extend_existing': True}

        target_name = db.Column(db.String(64), nullable=False, unique=True, comment="名称")
        target_id = db.Column(db.String(32), comment="ID")
        country = db.Column(db.String(32), comment="国家")
        time = db.Column(db.DateTime, comment='时间')
        content= db.Column(db.Text)
        the_geom = db.Column(Geometry(geometry_type='POINT', srid=4326), comment='经纬度')

    return BaseModel


@manager.command
def init_db():
    import json
    # 1. 获取数据列表
    with open('data_list.json', 'r') as f:
        ship_list = json.load(f)
    # 2. 动态创建数据库表
    for datain data_list:
        data_id = data['ID']
        get_model(data_id)

    with app.app_context():
        db.create_all(app=app)

    print('create table success')

参考

1.  ORM查询总结版 - osc_pocs16xx的个人空间 - OSCHINA - 中文开源技术交流社区
2. SQLAlchemy数据库增删改查 - 简书
3. 异步 I/O（异步） - SQLAlchemy 中文文档 - 文江博客