Python orm-sqlalchemy operation using

Python Operations Database

Using Orm-sqlalchemy,pymsql

Installation:

Pip Install Pymsqpip Install SQLAlchemy

First, " Connect to Database "

 "' Import the required package '
fromimport  create_enginefromimport  declarative_basefrom  Import Sessionmaker

# Basic Settings HOSTNAME='127.0.0.1'   #Local LiunxPOST ='3306'            #MySQL default portDATABASE ='MyDB'        #Database nameUSERNAME ='Admin'       #User namePASSWORD ='root110qwe'  #Password

# fixed notation
' Mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8 ' . Format (    USERNAME,    PASSWORD,    HOSTNAME,    POST,    DATABASE)
# The Db_url here is the path to the connection database. "MYSQL+MYSQLDB" Specifies the use of Mysql-python to connect

 # Create_engine () returns a database engine that displays each executed SQL statement when the echo parameter is True, which can be closed in a production environment.
Base=declarative_base (engine) # 创建对象的基类
 Session= Sessionmaker (engine)    # Create session class with database connection         
 Session = Session ()    # session can be treated as a database connection 
 # Sessionmaker () generates a database session class. An instance of this class can be used as a database connection, which also records the data of some queries and decides when to execute the SQL statement. 

if __name__ ' __main__ ':  #  Test whether the connection succeeded    connection = engine.connect (    )= Connection.Execute ('select 1')    print( Result.fetchone ())

Second, " Create a class-table mapping relationship "

The database is also mapped while the table is created, or you can create a table separately.

#-*-coding:utf-8-*- fromDatetimeImportdatetime fromSQLAlchemyImportColumn,integer,string,datetime fromConnectImportBase,session#There is no direct execution of the file after adding a dot to the connect front, but it can be called externally" "CREATE TABLE User" "classUser (Base):__tablename__='User'ID= Column (integer,primary_key=true,autoincrement=true)#primary key, self-growthUsername = Column (String (), Nullable=false)#User name length 20, non-emptyPassword = Column (String (50))#Password LengthCreatime = Column (Datetime,default=datetime.now)#creation Time@classmethoddefBy_name (cls,name): # define query function, return query result returnSession.query (CLS). Filter (cls.username==name). First ()def __repr__(self):#rewrite repr for easy reading        return "<user (id=%s,username%s,password=%s,createtime=%s) >"%(Self.id, Self.username, Self.password, Self.creatime) fromSqlalchemy.ormImportRelationship fromSQLAlchemyImportForeignKey" "Create a table user_details" "classuserdetails (Base):__tablename__='user_details'ID= Column (Integer, Primary_key=true, autoincrement=True) Id_card= Column (integer,nullable=true,unique=True) Lost_login=Column (DateTime) Login_num= Column (integer,default=0) user_id= Column (Integer,foreignkey ('user.id'))    " "the name of the table to be associated with the User Backref returns the Details method (property) Uselist default to True, indicating a one-to-many relationship (false means one-to-one) Cascade Automatic processing of the relationship is equivalent to the on in MySQL DELETE is similar to having 7 optional parameters in the Code layer control" "Userdetail= Relationship ('User', backref='Details', uselist=false,cascade=' All')    def __repr__(self): # rewrite repr for easy reading          return '<userdetails (id=%s,id_card=%s,last_login=%s,login_num=%s,user_id=%s) >'%(Self.id, Self.id_card, Self.lost_login, self.login_num, self . user_id) fromSQLAlchemyImportTable" "another way to create a table this method has no mapping, and if you need to recreate the class, rewrite the repr" "user_article= Table ('user_article', Base.metadata, Column ('user_id', Integer,foreignkey ('user.id'), primary_key=True), Column ('article_id', Integer,foreignkey ('article.id'), primary_key=True))

" "Create a table article" "classarticle (Base):__tablename__='article'ID= Column (integer,primary_key=true,autoincrement=True) Content= Column (String (), nullable=True) Create_time= Column (datetime,default=DateTime.Now ()) Article_user= Relationship ('User', backref='article', secondary=user_article)#To create a mapping relationship    def __repr__(self): # rewrite repr for easy reading          return  'article (id=%s,content=%s,create_time=%s)'%(Self.id, Self.content, Self.create_time)if __name__=='__main__': Base.metadata.create_all ()  # Execution 

Third, " simple increase, delete, check, change operation "

#-*-coding:utf-8-*- fromConnectImportSession fromUser_moduleImportUserdefAdd_user ():#Increase    #Persson = User (username= ' Xiaohong ', password= ' qwe123 ', id=1)    #Session.add (Persson) # submit a single data    #commit more than one data at a time, note that here all is the listSession.add_all ([User (username='Little Red', password='qwe123mmm'), User (username='Lao Wang', password='12345nnnnn'), User (username='Xiao Ming', password='654321hhhhh'),]) Session.commit ()#must be submitteddefSearch_user ():#querying the default repr is easy for machine reading and needs rewritingrows =session.query (User). All ()Print(rows)defUpdate_user ():#ChangeSession.query (User). Filter (user.id==2). Update ({user.password:1}) Session.commit ()defDelete_user ():#Delete    #rows = Session.query (User). Filter (user.id==2) [0]rows =session.query (User). All ()Print(rows) forIinchrows: # If the amount of data found is large, it is to use the loop to delete  session.delete (i) session.commit ()if __name__=='__main__': Add_user ()#Search_user ()    #Delete_user ()    #Update_user ()

Recommended article: Use of Liaoche sqlalchemy

Scripting House Python SQLAlchemy Basic operations and common tricks (with lots of examples, very good)

Z+j's How to use Python sqlalchemy

preliminary use of the sqlalchemy of K.takanashi　

　　　　 SQLAlchemy Introduction (i)

Python orm-sqlalchemy operation using