SQLAlchemy (1)--Python's SQLAlchemy and ORM

Python's SQLAlchemy and ORM (Object-relational Mapping: Object-relational mapping)

A common task in web programming is to create a valid background database. Previously, programmers were sent to the database engine by writing SQL statements, parsing and returning an array of records. Now, programmers can write ORM programs to replace the previously inflexible, difficult-to-maintain SQL statements that are tedious and error-prone.

ORM is a programming technique used in object-oriented programming languages to transform data between incompatible type systems (incompatible type System). The type systems commonly used in OO languages, such as Python-contained types, are non-scalar, meaning that these types cannot be expressed using primitive types such as integers, strings. For example, a person object might contain a list of address objects, and a list of PhoneNumber objects. Similarly, an address object may contain a postcode object, a StreetName object, and a Streetnumber object. Although simple objects such as postcode and streetname can be represented by strings, more complex objects such as address and person cannot be represented by strings or numbers. In addition, these complex objects also have instances or class methods that are not simply represented by strings or shaped numbers.

To deal with these complex object management problems, ORM has been designed. Our example above can be represented by an ORM system: Design a person class, address class, PhoneNumber class, and each class is mapped to a table in the database. This eliminates the need to write lengthy data interface programs and to focus more on the logical design of the system.

Code to write the database in Python (old way)

Create two tables using Pymysql:

Write the Python script pymysql_ex.py and execute:

$ python pymysql_ex.py

#!/usr/bin/env python# _*_ coding:utf-8 _*_import pymysql# Create Link conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' ABCe ', passwd= ' ABCE ', db= ' ABCE ', charset= ' utf8 ') #创建游标c = Conn.cursor () #执行sql建表, inserting content c.execute ('          CREATE table Person          (ID integer primary key, name varchar () "NOT null          ") c.execute ("'          CREATE TABLE address          (ID Integer primary key, Street_name varchar (), street_number varchar (),           post_code varchar () NOT NULL, Person_ ID integer NOT NULL,           foreign KEY (person_id) references person (ID))          ') c.execute ("'          insert INTO person VALUES (1, ' pythoncentral ')          ') c.execute ("          insert into address values (1, ' Python Road ', ' 1 ', ' 00000 ', 1)          #提交conn. Commit () #关闭游标c. Close () #关闭连接conn. Close ()

Write a script pymysql_q.py view the contents of a database table:

#!/usr/bin/env python# _*_ coding:utf-8 _*_import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' ABCe ', passwd= ' ABCE ', db= ' ABCE ', charset= ' utf8 ') #创建游标c = Conn.cursor () #执行sql查看表内容c. Execute (' select * from person ') Print (C.fetchall ()) C.execute (' SELECT * From address ') print (C.fetchall ()) #关闭游标c. Close () #关闭连接conn. Close ()

$ Python pymysql_q.py ((1, U ' pythoncentral '),) ((1, u ' Python road ', U ' 1 ', U ' 00000 ', 1),)

In the example we make the Pymysql connection commit the modifications to the database and use the PYMYSQL cursor to execute various SQL statements. Although these SQL statements do work, it is not easy to maintain the SQL statement itself. Let's look at how SQLAlchemy is mapped between the Python class and the table.

　　

Python ' s SQLAlchemy and declarative

Writing SQLAlchemy code has three important components:
--Tables in the database
--mapper: Mapping a Python class to a table in a database
--Class object, which defines how the records of a database are mapped to a Python object

There is no need to write tables, Mapper, class code in different places, SQLAlchemy declarative supports defining tables, mapper, and class objects into a class.

Create a declarative (sqlalchemy_declarative.py) below

#!/usr/bin/env python# _*_ coding:utf-8 _*_import pymysqlimport osimport sysfrom sqlalchemy import Column, ForeignKey, Int Eger, stringfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import Relationshipfrom SQLAlchemy Import Create_engine base = Declarative_base () class person (Base): __tablename__ = "Person" # Here we def    INE columns for the table person # Notice, each column is also a normal Python instance attribute.  id = column (Integer, primary_key=true) name = Column (String), Nullable=false) class Address (Base): __tablename__    = ' Address ' # Here we define columns for the table address.    # Notice that each column is also a normal Python instance attribute. id = column (Integer, primary_key=true) street_name = Column (string)) Street_number = Column (string)) post _code = Column (String (), nullable=false) person_id = Column (Integer, ForeignKey (' person.id ')) person = Relationsh IP (person) # Connection database is mapped using PYMYSQ moduleThe following parameter is the maximum number of connections 5engine = Create_engine (' Mysql+pymysql://abce:[email protected]:3306/abce?charset=utf8 ', max_ overflow=5) # Create All tables in the engine. This is equivalent to ' Create Table ' # statements in Raw SQL. Base.metadata.create_all (Engine)

Executes the script, the corresponding database table is created

$ python sqlalchemy_declarative.py

　　

Next, we insert some data (sqlalchemy_insert.py)

#!/usr/bin/env python# _*_ coding:utf-8 _*_import pymysqlfrom sqlalchemy import create_enginefrom sqlalchemy.orm import s Essionmaker from sqlalchemy_declarative import Address, Base, person engine = create_engine (' mysql+pymysql://abce:[ Email protected]:3306/abce?charset=utf8 ') # Bind the engine to the metadata of the Base class so that the# declarative s can be accessed through a dbsession InstanceBase.metadata.bind = engine Dbsession = Sessionmaker (bind=engine) # a Dbsessi On () instance establishes all conversations with the database# and represents a "staging zone" for all the objects loaded into the# database Session object.  The made against the objects in the# session won ' t is persisted into the database until you call# session.commit (). If you're not happy on the changes, you can# revert all of them back to the last commit by calling# Session.rollback () Session = Dbsession () # Insert a person Tablenew_person = person (name= ' new Person ') Session.add (New_person) Session.commit () # Insert an address in the address tablenew_address = Address (post_code= ' 00000 ', Person=new_person) sess Ion.add (new_address) Session.commit ()

You can see the data directly from the database background:

Mysql> SELECT * from person;+----+------------+| ID | Name       |+----+------------+|  1 | New person |+----+------------+1 row in Set (0.00 sec) mysql> SELECT * from address;+----+-------------+--------------- +-----------+-----------+| ID | Street_name | Street_number | Post_code | person_id |+----+-------------+---------------+-----------+-----------+|  1 | NULL        | NULL          | 00000     |         1 |+----+-------------+---------------+-----------+-----------+1 row in Set (0.00 sec) mysql>

　　

Of course we have to focus on looking at the inserted data from Python:

>>> from sqlalchemy_declarative import person, Base, address>>> from SQLAlchemy import create_engine& gt;>> engine = create_engine (' Mysql+pymysql://abce:[email protected]:3306/abce?charset=utf8 ') >> > Base.metadata.bind = engine>>> from sqlalchemy.orm import sessionmaker>>> dbsession = Sessionmaker () >>> Dbsession.bind = engine>>> session = Dbsession () >>> # Make a query to find AL L Persons in the database>>> session.query (person). All () [<sqlalchemy_declarative. Person Object @ 0x21c7390>]>>>>>> # Return The first person from all Persons in the database>> > person = session.query (person). First () >>> Person.nameu "New Person" >>>>>> # Find All Address whose person field was pointing to the person object>>> session.query (address). filter (Address.person = = PE Rson). All () [<sqlalchemy_declarative. Address Object at 0X22B08D0&GT;]&GT;&GT;&GT;&GT;>> # Retrieve One Address whose person field was point to the person object>>> session.query (Address). filt ER (Address.person = = person). One () <sqlalchemy_declarative. Address object at 0x22b08d0>>>> address = session.query (address). filter (Address.person = = person). One () >>> Address.post_codeu ' 00000 '

　　

Summarize
The above is how to use SQLAlchemy's declaratives to write database code. It is more object-oriented, easier to understand and maintain than traditional SQL statements.

Reference Original address: http://pythoncentral.io/introductory-tutorial-python-sqlalchemy/

SQLAlchemy (1)--Python's SQLAlchemy and ORM