Discuss / Python / 对于没有sql知识的我来说 操作数据库问题真多
Back

对于没有sql知识的我来说 操作数据库问题真多

Topic source

南柯一梦兯

#1 Created at ... [Delete] [Delete and Lock User]

from sqlalchemy import Column,Integer,String,create_engine,ForeignKey from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy.ext.declarative import declarative_base

#定义对象类指向表 Base=declarative_base() class User(Base): tablename = 'user' id = Column(Integer,primary_key=True) name = Column(String(20)) score = relationship('Score')

def __str__(self):
    return 'User id:%d,name:%s,scores:%s'%(self.id,self.name,self.score)
__repr__=__str__

class Score(Base): __tablename__ = 'score' id = Column(Integer,primary_key=True) chinese_score = Column(Integer) math_score = Column(Integer) user_id = Column(Integer,ForeignKey('user.id'))

def __str__(self):
    return 'chinese_score=%d,math_score=%d'%(self.chinese_score,self.math_score)
__repr__=__str__

engine = create_engine('mysql+pymysql://root:@localhost:3306/test2') DBSession = sessionmaker(bind=engine)

#创建对象session session=DBSession() #创建表user 以及 带外键的表score session.execute("create table user(id int primary key,name varchar(20))") session.execute('create table score(id int primary key,chinese_score int,math_score int,user_id int,constraint id_fk foreign key(user_id) references user(id))')

#插入表值 session.add(User(id=1,name='Ada')) session.add(User(id=2,name='Bob')) session.add(User(id=3,name='Mike')) session.add(Score(id=1,chinese_score=98,math_score=100,user_id=1)) session.add(Score(id=3,chinese_score=85,math_score=78,user_id=2)) session.add(Score(id=5,chinese_score=90,math_score=91,user_id=3))

#提交 关闭 session.commit() session.close()

session=DBSession() user=session.query(User).all() try: for info in user: print(info) finally: session.close()

南柯一梦兯

#2 Created at ... [Delete] [Delete and Lock User]

try finally 后应有制表符

南柯一梦兯

#3 Created at ... [Delete] [Delete and Lock User]

#输出 User id:1,name:Ada,scores:[chinese_score=98,math_score=100] User id:2,name:Bob,scores:[chinese_score=85,math_score=78] User id:3,name:Mike,scores:[chinese_score=90,math_score=91]

  • 1

Reply