Getting started with Python's sqlalchemy framework

This article focuses on the introduction of Python's SQLAlchemy framework, the SQLAlchemy framework is one of the ORM frameworks used in Python to manipulate databases, and the friends you need can refer to the

A database table is a two-dimensional table that contains multiple rows and columns. To represent the contents of a table in Python's data structure, you can use a list to represent multiple lines, each of which is tuple, representing a row of records, such as a user table that contains IDs and name:

?

1 2 3 4 5

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

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

But it's hard to see the structure of a table with tuple. If a tuple is represented by a class instance, the structure of the table can be more easily seen:

?

1 2 3 4 5 6 7 8 9 10

Class User (object): Def __init__ (self, ID, name): self.id = id self.name = name [User (' 1 ', ' Michael '), User (' 2 ', ' Bob ') , User (' 3 ', ' Adam ')]

This is the legendary ORM technology: Object-relational Mapping, mapping the table structure of relational databases to objects. Is it simple?

But who's going to do the conversion? So the ORM framework emerges.

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

First install SQLAlchemy via Easy_install or PIP:

?

1	$ Easy_install SQLAlchemy

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20-21

# import: From SQLAlchemy import Column, String, create_engine from sqlalchemy.orm import Sessionmaker from Sqlalchemy.ext.dec Larative Import Declarative_base # Create base class for object: Base = Declarative_base () # define User object: Class User (Base): # Table Name: __tablenam e__ = ' user ' # Structure of table: id = column (string (), primary_key=true) name = Column (String (20)) # Initialize database connection: engine = Create_e Ngine (' Mysql+mysqlconnector://root:password@localhost:3306/test ') # Create dbsession type: dbsession = Sessionmaker (bind= Engine

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

?

1 2 3 4

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

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

' 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 to a database table.

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

?

1 2 3 4 5 6 7 8 9 10

# 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 a user object. The query interface provided by SQLAlchemy is as follows:

?

1 2 3 4 5 6 7 8 9

# Create session:session = Dbsession () # Create query queries, filter is where condition, last call one () returns a unique row, and all rows are returned if all () is called: User = Session.query (user ). Filter (user.id== ' 5 '). One () # Print the Name property of type and object: print ' type: ', type (User) print ' name: ', User.Name # close Session:session.cl OSE ()

The results of the operation are as follows:

?

1 2	Type: <class ' __main__. User ' > Name:bob

As you can see, ORM is about associating rows of database tables with corresponding objects and converting them to each other.

Because multiple tables in a relational database can also use a foreign key to achieve one-to-many, Many-to-many, and so on, the ORM Framework can also provide a one-to-many, many-to-many, and multiple function between two objects.

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

?

1 2 3 4, 5 6 7 8 9 10 11 12 13 14 15

Class User (Base): __tablename__ = ' user ' id = column (string (), primary_key=true) name = Column (String (20)) # One-to-many: Book s = Relationship (' book ') class book (Base): __tablename__ = ' book ' id = Column (String (), primary_key=true) name = Col Umn (String (20)) # The Book Table of the side of "many" is associated with a foreign key to the user table: user_id = Column (String), ForeignKey (' user.id '))

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

Summary

The ORM Framework's role is to automatically convert a row of records from a database table to one object.

The premise of using ORM correctly is to understand the principles of relational databases.

Note <> : More Wonderful tutorials please focus on Triple programming