Python's SQLAlchemy module connects and operates on MySQL's basic example _python

Introduction of SQLAlchemy
SQLAlchemy is an Open-source SQL Toolkit, an object-relational mapper published by the MIT license for the basic Python programming language. SQLAlchemy provides "a well-known enterprise-class suite of persistence patterns, one of the advantages of using independent sqlalchemy such as ORM is that it allows developers to first consider the data model and decide how to visualize the data later."
second, the installation of Sqlalchempy
first need to install MySQL, here is no longer said ...
Then, download SQLAlchemy (http://www.sqlalchemy.org/download.html), here we take the Windows system for example, and then open cmd, in the installation package file directory, run

Python setup.py Install

, input via Python

Import SQLAlchemy

, execution of the error indicates that the installation was successful

Iii. Examples of the use of SQLAlchemy

1, complete the Simple data table information inquiry

# 1. Import module from
sqlalchemy Import *
sqlclchemy.orm Import *
2. Establish database engine
Mysql_engine = Create_engine ("$ Address ", Echo, module"
 #address database://username: password (null without password) @ Host name: Port/Database name
 # The Echo ID is used to set up the SQLAlchemy log system completed through the Python standard log module, and when logging is turned on, we will be able to see all the SQL generated code
# 3. Establish a connection
connection = Mysql_ Engine.connect ()
# 4. Query table information Result
= Connection.Execute ("Select name from T_name" for row in result
:
 Print "Name:", row[' name '
# 5. Close connection
Connection.close ()

2, insert a new data table

# 1. Import module from sqlalchemy import * sqlclchemy.orm import * # 2. Establish database Engine Mysql_engine = Create_engine ("$address", Echo, module) #address database://username: password (null without password) @ Host name: Port/Database name # The Echo ID is used to set up the SQLAlchemy log system completed through the Python standard log module, and when logging is turned on, we will be able to see all the SQL generated code # 3. Set the metadata and bind it to the database engine metadata = metadata (mysql_engine) # 4. Define the table you want to create new users = Table (' Users ', Metadata,column (' user_id ', Integer, Primary_key=true), Column (' name ', String), Colum N (' age ', Integer), Column (' password ', String), #Table实现方式与SQL语言中的CRETE table is similar to # 5. Create a table Metadata.create_all (mysql_engine) #向数据库发出CREATE tables command in the database, this database creates a new table #调用时会检查已经存在的表结构 named users, so you can call # 6 repeatedly. Create a Python class User (): def __int__ (self, name, fullname, password) that matches the users table in the database: Self.name = name Self.fullname = FullName self.passwd = passwd #python类的属性需与users表的列名一致 # 7. Set mapping from Sqlalchemy.orm Import Mapper mapper (user, users) # Mapper () creates a new mapper object that is associated #需要注意的是 with the defined class. Data tables that are mapped through mapper must have a primary key, and if there is no primary key to locate a row of a table, #如果无法定位某行row, you cannot do Object-relatiOnal mapping such a mapping # 8.
Creating Session Sessions = Sessionmaker (Bind=mysql_egnine) session = Session () #由此我们只需对python的user类的操作, the actual implementation of the backend database is completed by session # 9. Execute Session.commit () #实现与数据库的交互 # 10. Query Usr_info = session.query (user). filter_by (age=12). #返回数据库中年纪12岁的第一条数据

The above is combined with the ORM part of SQLAlchemy to implement a Mapper object, the instance of the class corresponds to the record in the table, and the property of the instance corresponds to the field. Implementing a data mapping requires three elements: Tabella Metadata, user-defined class, Mapper object, these three are the basic elements to realize object to table mapping, on the basis of which, we can realize one-to-many mapping and realize the problem of similar multiple table query.
First, create two associated table student, Score, in table Score, the ID field of the primary table is a foreign key

Student = Table (' Student ', Engine,  
              column (' id ', interger, Primary_key = True), 
              column (' Name ', String, nullable= False), 
              column (' Age ', Interger) 
) 
Score = Table (' Score ', Engine,  
            column (' id ', Integer, primary_key= True), 
            column (' student_id ', Integer, ForeignKey (student.id)) 
            column (' Category ', String, Nullable=false), 
            column (' Score ', Integer)   
 

In both tables, the score table takes the ID entry in the student table as the foreign key, generally called the student table as the main table, and the score table is from the table
Once the table is created, then again, in Python you need to define two classes that correspond to the table

Class Student_type (object): 
     def __init__ (self): 
        self.name = None 
class Score_type (object): 
     def __ Init__ (self): 
        self.category = None 

When we build mapping, we just need to embody two of tables and relate to each other,
Does not care about the relationship between the specific primary key and foreign key in the table (handled by SQLAlchemy),
When the need to embody the relationship between table student and table score, mapper specific definition methods such as:
Mapper (Student_type, student, properties={' _scores ': relation (Score_type, score)})
Through the properties of parameters, to achieve score_type and score mapping,
This allows you to query the values in the score table by accessing the ' _scores ' attribute in student
In addition, properties is a dictionary that can add multiple properties, sqlalchemy some modules, such as Backref, can also be imported

On the other hand, using relational mapping makes it easy to find the corresponding object directly from an object.