Getting started with Python's sqlalchemy framework

A database table is a two-dimensional table that contains multiple rows and columns. If the contents of a table are represented by a Python data structure, you can use a list to represent multiple rows, and each element of the list is a tuple representing a row of records, such as a user table with ID and name:

[  (' 1 ', ' Michael '),  (' 2 ', ' Bob '),  (' 3 ', ' Adam ')]

The data structure returned by Python's db-api is represented as above.

But it is hard to see the structure of a table with a tuple. If a tuple is represented by a class instance, it is easier to see the structure of the table:

Class User (object):  def __init__ (self, ID, name):    self.id = id    self.name = name[  User (' 1 ', ' Michael '), C7/>user (' 2 ', ' Bob '),  User (' 3 ', ' Adam ')]

This is the legendary ORM technique: Object-relational Mapping, which maps the table structure of a relational database to an object. Isn't it simple?

But who will do the conversion? So the ORM framework emerges.

In Python, the most famous ORM framework is sqlalchemy. Let's take a look at the usage of SQLAlchemy.

First install SQLAlchemy via Easy_install or PIP:

$ Easy_install SQLAlchemy

Then, using the user table that we created in the MySQL test database last time, try it with SQLAlchemy:

The first step is to import the SQLAlchemy and initialize the dbsession:

# import: From SQLAlchemy import Column, String, create_enginefrom sqlalchemy.orm import Sessionmakerfrom Sqlalchemy.ext.declarative Import declarative_base# creates the base class for the object: base = Declarative_base () # Defines the User object: class User (Base):  # Table Name:  __tablename__ = ' user '  # table structure:  id = column (String), primary_key=true)  name = Column ( String (20)) # Initialize database connection: engine = Create_engine (' mysql+mysqlconnector://root:password@localhost:3306/test ') # Create Dbsession type: dbsession = Sessionmaker (bind=engine)

The above code completes the initialization of the SQLAlchemy and the class definition for each table. If you have more than one table, continue to define other classes, such as school:

Class School (Base):  __tablename__ = ' School '  id = ...  Name = ...

Create_engine () is used to initialize the database connection. SQLAlchemy represents the connection information with a string:

' Database type + database driver name://user name: password @ machine Address: Port number/database name '

You only need to replace the user name, password and other information as needed.

Next, let's look at how to add a row of records to a database table.

With ORM, we add a row of records to a database table and can be thought of as adding a user object:

# Create Session object: Session = Dbsession () # Create new User object: New_user = User (id= ' 5 ', name= ' Bob ') # Add to Session:session.add (New_user) # Commit is saved to database: Session.commit () # Close Session:session.close ()

Visible, the key is to get the session, then add the object to the session, and finally commit and close. The session object can be considered the current database connection.

How do I query data from a database table? With ORM, the query can be no longer a tuple, but the user object. The query interfaces provided by SQLAlchemy are as follows:

# Create session:session = Dbsession () # Create a query, filter is the Where condition, and last Call one () returns a unique row, if all () is called All rows are returned: User = Session.query (user ). Filter (user.id== ' 5 '). One () # print Type and Object Name property: print ' Type: ', type (User) print ' name: ', user.name# close session: Session.close ()

The results of the operation are as follows:

Type: 
 
  
   
  name:bob
 
  

As can be seen, ORM is the database table of the row with the corresponding object to establish an association, and convert each other.

Since multiple tables in a relational database can also be used to implement one-to-many, many-to-many associations with foreign keys, the ORM Framework can also provide one-to-many, many-to-many functions between two objects.

For example, if a user has more than one book, you can define a one-to-many relationship as follows:

Class User (Base):  __tablename__ = ' user '  id = column (string (), primary_key=true)  name = Column (String (20 )  # One-to-many:  books = relationship (' book ') class book (Base):  __tablename__ = ' book '  id = Column (String (20), primary_key=true)  name = Column (String) # The Book  table for the "many" side is associated to the user table by a foreign key:  user_id = Column (String (20), ForeignKey (' user.id '))

When we query a user object, the books property of the object returns a list containing a number of book objects.
Summary

The purpose of an ORM framework is to automatically convert a row of records from a database table to an object.

The prerequisite for proper use of ORM is to understand the principles of relational databases.

