Python connects to MySQL using the SQLAlchemy module

ORM Technology: Object-relational Mapping is responsible for mapping the table structure of a relational database to an object.

1. Installing the SQLAlchemy module

Pip Install SQLAlchemy

2. Initializing the connection

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4  fromSQLAlchemyImportColumn, Create_engine5  fromSqlalchemy.typesImport*6  fromSqlalchemy.ormImportSessionmaker7  fromSqlalchemy.ext.declarativeImportDeclarative_base8 9 #connecting to a databaseTenSql_connect =' Mysql://user:[email protected]:p ort/database ' OneEngine =create_engine (sql_connect) A #To Create a dbsession type: -Dbsession = Sessionmaker (bind=engine)

Note: Red part: Database type://user name: password @ machine Address: Port number/database name.

The complete is: Database type + database driver://user name: password @ machine Address: Port number/database name

3.自动建表

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4  fromSQLAlchemyImportColumn5  fromSqlalchemy.typesImport*6  fromSqlalchemy.ext.declarativeImportDeclarative_base7  fromSQLAlchemyImportCreate_engine8  fromSqlalchemy.ormImportSessionmaker9 Ten #connecting to a database OneSql_connect ='Mysql://ruser:[email protected]:p ort/datbase' AEngine =create_engine (sql_connect) -Dbsession = Sessionmaker (bind=engine) - #to create a base class for an object: theBasemodel =declarative_base () -  - #Defining Objects - classUser (Basemodel): +     #Table name -     __tablename__='User' +     #Table Structure Aid = Column (String), primary_key=True) atName = Column (String (20)) -Age =Column (Integer) -  - #Initializing the database - definit_db (): - BaseModel.metadata.create_all (Engine) in  - #Delete all data tables to defdrop_db (): +BaseModel.metadata.drop_all (Engine)

4. Add Data

1 #creates a Session object, equivalent to a cursor inside a mysqldb2Session =dbsession ()3 #To create a new user object:4New_user = User (id='2', name='John', age=13)5 #Add to session:6 Session.add (New_user)7 #commit is saved to the database8 Session.commit ()9 #Close SessionTenSession.close ()

5. Querying data

The WHERE clause in the query statement is substituted with filter (), but the matching value needs to be "= =", and "=" is used if filter_by () is used.

Query one:

1 #To create a session:2Session =dbsession ()3 #Create a query, filter is the Where condition, and the last Call to one () returns a unique row, and all rows are returned if all () is called:4user = Session.query (user). filter (User.Name = ='John', User.age > 12). One () 5 #print type and object's Name property:6 Print 'Type:', type (user)7 Print 'Age :', User.age8 #Close session:9Session.close ()

Query more than one:

11#To create a session:22 Session =dbsession ()33#Create a query, filter is the Where condition, and the last Call to one () returns a unique row, and all rows are returned if all () is called:44 users = Session.query (User). Filter (User.Name = ='John'). All ()55#print type and object's Name property:66Print 'Type:', type (users)77 forUinchUsers:88Printu.id99#Close session:TenTen Session.close ()

6. Updating data

Way One:

1 #To create a session:2Session =dbsession ()3 #multiple data updates available4user = Session.query (user). filter (User.ID = ='5')5User.update ({user.age:14})6 #Submit Data7 Session.commit ()8 #Close Session9Session.close ()

Way two:

# Create session:session = dbsession ()#  can be updated with multiple data user = Session.query (user). filter_by (id= ' 5 ')=#  submit data Session.commit ()#  Close SessionSession.close ()

7. Delete Data

1 #Create session2Session =dbsession ()3 #What data is deleted4user = Session.query (user). filter (User.ID = ='6'). One ()5 session.delete (user)6 #Submit Data7 Session.commit ()8 #Close Session9Session.close ()

Python connects to MySQL using the SQLAlchemy module