fromsqlalchemyimportColumn,String,create_engine,Integer,Textfromsqlalchemy.ormimportsessionmakerfromsqlalchemy.ext.declarativeimportdeclarative_baseimporttime#创建对象的基础:Base=declarative_base()#定义用户对象:classUser(Base):#表的名称:__tablename__='users'#表示的结构:id=Column(Integer,autoincrement=True,primary_key=True,unique=True,nullable=False)name=Column(String(50),nullable=False)sex=Column(String(4),nullable=False)nation=Column(String(20),nullable=False)birth=Column(String(8),nullable=False)id_address=Column(Text,nullable=False)id_number=Column(String(18),nullable=False)creator=Column(String(32))create_time=Column(String(20),nullable=False)updater=Column(String(32))update_time=Column(String(20),nullable=False,default=time.strftime("%Y-%m-%d%H:%M:%S",time.localtime()),onupdate=time.strftime("%Y-%m-%d%H:%M:%S",time.localtime()))comment=Column(String(200))#初始化数据库连接：engine=create_engine('postgres://postgres:123123@localhost:5432/postgres')#Username:password@localhost:port/databaseName#创建DBSessiontype:DBSession=sessionmaker(bind=engine)defcreateTable():#创建表Base.metadata.create_all(engine)definsertData():#插入操作#创建会话session=DBSession()#创建新的用户对象:local_time=time.strftime("%Y-%m-%d%H:%M:%S",time.localtime())new_user=User(name='hsh4',sex='male',nation='Chinese',birth='19991012',id_address='广州大学',id_number='441242142142',create_time=local_time)#添加到session:session.add(new_user)#提交并保存到数据库:session.commit()#关闭session:session.close()defselectData():#查询操作#创建Sessionsession=DBSession()#创建Query查询，filter是where条件，最后调用one()返回唯一的行，如果调用all()然后返回所有行：user=session.query(User).filter(User.id=='1'andUser.name=='hsh4').one()print('name:',user.name)print('id_address:',user.id_address)session.close()#关闭会话defupdateData():#更新操作session=DBSession()#创建会话users=session.query(User).filter_by(name="hsh4").first()#查询条件users.id_number="abcd"#更新操作session.add(users)#添加到sessionsession.commit()#提交并保存到数据库session.close()#关闭sessiondefdeleteData():#删除操作session=DBSession()#创建sessiondelete_users=session.query(User).filter(User.id=="1").first()ifdelete_users:session.delete(delete_users)session.commit()session.close()#关闭会话defdropTable():#删除表session=DBSession()#创建会话session.execute('droptableusers')session.commit()session.close()#createTable()#insertData()#selectData()#updateData()#deleteData()
