Python development "Chapter 12": ORM SQLAlchemy

I. Object mapping relationship (ORM)

ORM English Full Name Object relational mapping, that is, objects mapping relation program, simply say we like python this object-oriented program is all objects, but we use the database is relational, in order to ensure consistent use of the habit, By ORM, the object model of the programming language and the relational model of the database are mapped, so that we can use the programming language to manipulate the database directly using the programming language object model, instead of using the SQL language directly

Advantages:

• Hidden data access details, "closed" Universal database interaction, ORM Core. He makes our universal database interaction simple and easy, without having to think about the damned SQL statements at all. Rapid development, resulting from
• ORM makes it easy to construct solidified data structures

Disadvantages:

• Inevitably, automation means mapping and association management at the expense of performance (early, this is what all dislike orm people have in common). Now the various ORM frameworks are trying to use various methods to alleviate this (lazyload,cache), the effect is very significant

Second, SQLAlchemy

In Python, the most famous ORM framework is sqlalchemy. Users include well-known companies or applications such as Openstack\dropbox

Dialect is used to communicate with the data API, invoking different database APIs depending on the configuration file, enabling operations on the database, such as:

Mysql-python    mysql+mysqldb://<user>:<password>@
Note: Support for connecting MySQL, oracles database
Installation:
Pip Install Sqlalchemypip install pymysql  #由于mysqldb依然不支持py3, so here we use Pymysql to interact with SQLAlchemy
1. Basic use
See MySQL Basics-"http://www.cnblogs.com/lianzhilei/p/5993282.html"
Here's where you'll witness the ORM's NB, before Pangu opens the heavens, we create a table like this:
CREATE TABLE User (    ID INTEGER not NULL auto_increment,    name varchar (+),    password varchar),    PRIMARY KEY (ID))
This is just the simplest SQL table, if you add a foreign key association or something, the general Programmer's brain capacity is not remember those SQL statements, so there is an ORM, to achieve the same function above, the code is as follows:
Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy import Column, Integer, String engine = Create_engine ("Mysql+pymysql://root:[email protected]/testdb",                                    Encoding= ' Utf-8 ', echo=true)  Base = Declarative_base () #生成orm基类 class User (base):    __tablename__ = ' user ' #表名    id = column (Integer, primary_key=true)    name = Column (string ())    Password = column (string (64)) Base.metadata.create_all (engine) #创建表结构
You say, Niang that butt, and did not feel the code less quantitative, oh, children Mo Monkey Urgent, the show in the back
In addition to the above creation, there is a way to create a table, although not commonly used, but still look at it
 fromSQLAlchemyImportTable, MetaData, Column, Integer, String, ForeignKey fromSqlalchemy.ormImportMapper Metadata=MetaData () User= Table ('User', metadata, Column ('ID', Integer, primary_key=True), Column ('name', String (50)), Column ('FullName', String (50)), Column ('Password', String (12))        ) classUser (object):def __init__(self, name, fullname, password): Self.name=name Self.fullname=FullName Self.password=Password mapper (user, user)#The table metadata is created separately and the table construct, then associated with the User class via the mapper ( ) function
a different way
Python development "Chapter 12": ORM SQLAlchemy