python Operation Mysql③python operation MySQL ORM tool sqlaichemy installation Configuration and User manual address: http:docs.sqlalchemy.org/en/rel_1_1/orm/index.html Installation D:\software\source_tar>pip Install SQLAlchemy detects if the installation was successful D:\software\source_tar>Pythonpython3.5.2 (V3.5.2:4DEF2A2901A5, June, 22:18:55) [MSC v.1900 64bit (AMD64)] on Win32type" Help","Copyright","credits" or "License" forMore information.>>>Importoperation of Sqlalchemymysql ORM Library SQLAlchemy#Coding:utf-8 fromSqlalchemy.ext.declarativeImportDeclarative_base fromSQLAlchemyImportColumn, Integer, String, DateTime, Boolean fromSQLAlchemyImportCreate_engine fromSqlalchemy.ormImportSessionmaker" "ID int primary KEY auto_increment, Title varchar ($) NOT NULL, content varchar ($) NOT NULL, TPEs Varc Har () not NULL, image varchar (+) NULL, author varchar () NULL, View_count int default 0, Created_at datet IME NULL, is_valid smallint default 1" "#to create a base class for an objectBase =declarative_base ()#Initialize database connection, pay attention to Charset=utf8 otherwise Chinese cannot supportEngine = Create_engine ("mysql://root: @localhost/news?charset=utf8")#Create Dbsession typeDbsession = Sessionmaker (bind=engine)#defining the News objectclassNews (Base):__tablename__='News'ID= Column (Integer, primary_key=True) Title= Column (String (), nullable=False) Content= Column (String), nullable=False) Types= Column (String (Ten), nullable=False) Image= Column (String (300),) author= Column (String (20),) View_count=Column (Integer) created_at=Column (DateTime) is_valid=Column (Boolean)" "# Simple Test # If the table does not exist, create a table Base.metadata.create_all (engine) # Create Session object: Session = Dbsession () # Create a new User object, add a test data NEWS01 = News (title= ' title 1 ', content = ' content01 ', types = ' Baidu ', image = '/static/img/01.jpg ', author = ' Jack ', View_count = 3) # Add to Session:session.add (NEWS01) # Commit to save to database: Session.commit () # Close session:session.close ()" "#test class for ORMclassOrmtest (object):#Initialize Create session def __init__(self): self.session=dbsession ()#Add Data defAdd_one (self): New_obj=News (Title='title 20180202', Content='content 20180202', Types='Baccarat') Self.session.add (new_obj) self.session.commit ()returnNew_obj#add more than one piece of data defAdd_more (self): add_list= [] forIinchRange (10): New_obj= News (title='Title%s'%str (i), content='Content%s'%str (i), types='Hundred%s'%str (i)) Self.session.add (new_obj) add_list.append (new_obj) Self.session.commit () /c8>returnadd_list#Delete Data defDelete_data (self): Data= Self.session.query (News). Get (51) self.session.delete (data) self.session.commit ()#Modify A single piece of data defUpdate_One (Self, _id): obj=self.session.query (News). Get (_id)ifObj:obj.is_valid=0 self.session.add (obj) self.session.commit ()returnTruereturnFalse#modify more than one data defUpdate_data (self):#How to use filter_by #data_list = Self.session.query (News). filter_by (is_valid = 0) #How to use filterData_list = Self.session.query (News). Filter (News.id > 45) forDatainchdata_list:Print(data.title) data.is_valid= 1self.session.add (data) self.session.commit ()#get a piece of data defGet_one (self):returnSelf.session.query (News). Get (1) #get more than one piece of data defGet_more (self):returnSelf.session.query (News). filter_by (Is_valid = 1)defmain (): obj=ormtest ()#rst = Obj.add_one () #print (' id:%s, title:%s,content:%s,types =%s '% (rst.id,rst.title,rst.content,rst.types)) #add more than one piece of data #rst = Obj.add_more () #For _new in rst: #print (' id:{0},title{1},content:{2} '. Format (_new.id,_new.title,_new.content)) #Test Delete #Obj.delete_data () #Modify A single piece of data #print (Obj.update_one ()) #modify more than one dataObj.update_data ()#testing a function to get a single piece of data #rst = Obj.get_one () #print (rst.title) #get more than one piece of data #rst = Obj.get_more () #For _news in rst: #print (' News ID:%s, title:%s, content:%s '% (_news.id,_news.title,_news.content))if __name__=="__main__": Main ()
Python Operation Mysql③python operation MySQL ORM tool sqlaichemy installation configuration and use