Shulou(Shulou.com)06/01 Report--

This article will explain in detail about sqlalchemy based on mysql to achieve the method of adding, deleting and checking operations, Xiaobian feel quite practical, so share with you as a reference, I hope you can gain something after reading this article.

Requirements Scenario:

Boss asked me to write or update the data crawled by crawlers to mysql database. Baidu has two methods.

1 is to use pymysql connection mysql, through the operation of the original sql statement to add, delete and check the data;

2 is to use sqlalchemy to connect mysql, through the ORM model to build tables and operate databases, do not need to write native sql statements, relatively simple;

The following is the experience of using sqlalchemy.

Implementation process: Connect database Create table through model class Create session Execute table statement Add delete modify query through session

from sqlalchemy import exists, Column, Integer, String, ForeignKey, existsfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy. org import sessionmaker#database engine created = create_engine("mysql+pymysql://user:pwd@ip/database name? charset=utf8")#Create session type DBSession = sessionmaker (bind=engine)#instantiate the declarative model- Base is the ORM model Base = declarative_base()#create a service order table class ServiceOrder(Base):__tablename__ = 'serviceOrderTable' id = Column (Integer, primary_key=True, autoincrement=True) serviceOrderId = Column (String(32), nullable=False, index=True, comment='Service ID') serviceDesc = Column (String(268), comment ='service description') oneLevelName = Column (String(32), comment ='C category') twoLevelName = Column (String(32), comment ='T subclass') threeLevelName = Column (String(32), comment ='I item') fourLevelName = Column (String(32), comment='S subitem') transferTimes = Column (String(32), comment ='number of transfers') overDueStatus = Column (String(32), comment ='expired status') serviceTimeLimit = Column(String(32), comment ='service time') serTimeLimitTypeName = Column(String(16), comment ='time limit type') #One-to-many: # serviceWorkOrder = relationship ("ServiceWorkOrder", backref="serviceorder")#many-to-one: Multiple service orders can belong to serviceorder class ServiceWorkOrder(Base):__tablename__ = 'serviceWorkOrderTable' id = Column (Integer, primary_key=True, autoincrement=True) serviceWorkOrderId = Column (String(32), nullable=False, index=True, comment='Service Job ID') workOrderName = Column (String(268), comment ='job name') fromId = Column (String(32), comment ='Service Order ID') createUserSectionName = Column (String(32), comment ='create person room') createUserName = Column (String(32), comment ='Creator ') handler Name = Column (String(32), comment ='Processed By ') statusName = Column(String(32), comment ='Job Status') createTime = Column(String(32), comment ='Creation Time') #The book table of the "many" party is associated to the user table by a foreign key: # serviceOrder_id = Column (Integer, ForeignKey ('serviceOrderTable.id')#Create database Not created if database already exists Existing libraries are linked directly by library namedef init_db(): Base.metadata.create_all(engine)def drop_db(): Base.metadata.drop_all(engine)def insert_update():# all_needed_data_lists is the data format that needs to be inserted into the database [{key: value,... }, { }, { }...] for item in all_needed_data_lists: ServiceOrderRow = ServiceOrder(serviceOrderId=item['serviceOrderId'], serviceDesc=item['serviceDesc'], oneLevelName=item['oneLevelName'], twoLevelName=item['twoLevelName'], threeLevelName=item['threeLevelName'], fourLevelName=item['fourLevelName'], transferTimes=item['transferTimes'], overDueStatus=item['overDueStatus'], serviceTimeLimit=item['serviceTimeLimit'], serTimeLimitTypeName=item['serTimeLimitTypeName'], ) try: #Use exists to determine whether the target object exists, return True or Faults it_exists = session.query( exists().where(ServiceOrder.serviceOrderId == item['serviceOrderId'] ) ).scalar() except Exception as e: self.log.error(e) break try: #If it doesn't exist, add; if it exists, update existing data 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 ('Data update successful! ') except: session.rollback() self.log.info ('Data update failed! ')if __name__ == "__main__": #Create database If database already exists, it will not be created. It will directly connect existing library according to library name init_db() #Create session object, add, delete and modify: session = DBSession() #Use session to add and modify data Remember to submit insert_update() About the method of adding, deleting and checking mysql based on sqlalchemy, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.