Shulou(Shulou.com)06/01 Report--

SQLAlchemy of Python full Stack Road Series

SQLAlchemy's is the Python SQL toolkit and object-relational mapper, which gives application developers full functionality and SQL flexibility.

It provides a complete set of well-known enterprise persistence patterns designed for efficient and high-performance database access adapted into a simple Python domain language.

SQLAlchemy's philosophy

SQL databases behave unlike the larger size and performance start relationships of object collections; object collections do not behave like more abstract start relational tables and rows. The purpose of SQLAlchemy is to meet these two principles.

SQLAlchemy believes that a database is the engine of relational algebra, not just a collection of tables, where rows can be selected not only from the table, but also added and other select statements; any of these units can be combined into a larger structure, based on SQLAlchemy's expression language, from its core concept.

SQLAlchemy is the most famous object-relational mapper (ORM), providing a data mapping schema, where classes can be mapped to optional components in the database in an open, multiple way-allowing the object model and database schemas to develop clean separation from the start mode.

SQLAlchemy's overall thinking on these issues is that most other SQL/ORM tools are rooted in a completely different so-called complimentarity--oriented approach; rather than hiding behind the SQL and relational object detail automation wall, all processes are fully exposed in a series of combined, transparent tools. The library takes place in the work of automatic redundant tasks, while the developer still controls how the database is organized and how the SQL is constructed.

The main goal of SQLAlchemy is to change the way you deal with databases and SQL!

The use of SQLAlchemy

Connection to the database

MySQL-Python

Mysql+mysqldb://:@ [:] /

Pymysql

Mysql+pymysql://:@/ [?]

MySQL-Connector

Mysql+mysqlconnector://:@ [:] /

View version

> import sqlalchemy > sqlalchemy.__version__'1.0.14' creates and deletes tables

Single table creation

#! / usr/bin/env python# _ * _ coding:utf-8 _ * _ import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, Index, UniqueConstraint, ForeignKeyfrom sqlalchemy.orm import sessionmakerengine = create_engine ('mysql+pymysql://root:as@127.0.0.1:3306/tesql?charset=utf8' Echo=True) # echo=True output generated SQL statement Base = declarative_base () # generate an ORM base class class UserInfo (Base): _ _ tablename__ = 'UserInfo' # table name "" create field index=True generic index unique=T unique index "id = Column (Integer, primary_key=True, autoincrement=True) # primary_key= primary key Autoincrement= self-incrementing name= Column (String (32)) password = Column (String (16)) _ _ table_args__ = (Index ('id',' name'), # joint index UniqueConstraint ('name',' password', name='name_password') # joint unique index The name of the name index) # Let the queried data display Chinese def _ repr__ (self): return self.name Base.metadata.create_all (engine) # all the classes that integrate the Base class Create a table structure

The above code actually creates a UserInfo table with three fields. The actual SQL statement is as follows:

CREATE TABLE `UserInfo` (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR (32), password VARCHAR (16), PRIMARY KEY (id), CONSTRAINT name_password UNIQUE (name, password))

Because echo=True was added when the engine was created, the executed SQL will be output on the console so that we can troubleshoot the problem.

Create an one-to-many table

Class Favor (Base): _ _ tablename__ = 'favor' nid = Column (Integer, primary_key=True, autoincrement=True) caption = Column (String (50), default='red', unique=True) class Person (Base): _ _ tablename__ =' person' nid = Column (Integer, primary_key=True, autoincrement=True) favor_id = Column (Integer, ForeignKey ("favor.nid"))

Create many-to-many tables

# group class Group (Base): _ _ tablename__ = 'group' id = Column (Integer, primary_key=True) name = Column (String (64), unique=True, nullable=False) port = Column (Integer, default=22) # Server class Server (Base): _ _ tablename__ =' server' id = Column (Integer, primary_key=True, autoincrement=True) hostname = Column (String (64), unique=True, nullable=False) # server group The third table class ServerToGroup (Base): _ _ tablename__ = 'servertogroup' nid = Column (Integer, primary_key=True, autoincrement=True) server_id = Column (Integer, ForeignKey (' server.id')) group_id = Column (Integer, ForeignKey ('group.id'))

Delete tabl

Base.metadata.drop_all (engine) # delete all the classes that integrate the Base class, delete the table, manipulate the table and add data

Add a single piece of data

MySesion = sessionmaker (bind=engine) session = MySesion () # create a data users = UserInfo (name='Hello', password='World') # add data to the table session.add (users) # submit effective session.commit ()

How much data do you add?

Session.add_all ([UserInfo (name='A', password='1'), UserInfo (name='B', password='2')]) # submit session.commit () delete data session.query (UserInfo) .filter (UserInfo.name = ='a') .delete () session.commit () query

Get all the contents of a table

Result = session.query (UserInfo). All () print (result) modify data session.query (UserInfo) .filter (UserInfo.id = = 8). Update ({"name": "ffff"}) session.commit () query data

Get all

Result = session.query (UserInfo). All ()

Get the specified field

Result = session.query (UserInfo.name, UserInfo.password). All ()

Gets the specified

Result = session.query (UserInfo) .filter_by (name='b') .all () # returns a list

Get the first item

Result = session.query (UserInfo) .filter_by (name='b') .first () # get an attribute result.name in the value

Get the number of data occurrences

Result = session.query (UserInfo). Filter_by (name='b'). Count ()

Query using and_ and or_

Import and_, or_ Modul

From sqlalchemy import and_, or_

And_

For row in session.query (UserInfo) .filter (and_ (UserInfo.name = = 'Aids, UserInfo.password = = 1)): print (row)

Or_

For row in session.query (UserInfo) .filter (or_ (UserInfo.name = = 'Hello', UserInfo.password = = 1)): print (row)

Association query

Create the following database

#! / usr/bin/env python# _ * _ coding:utf-8 _ * _ from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmaker, relationshipsfrom sqlalchemy import Column, Integer, String, Index, UniqueConstraint, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_baseengine = create_engine ('mysql+pymysql://root:as@127.0.0.1:3306/tesql') Base = declarative_base () class Son (Base): _ _ tablename__ =' son' id = Column (Integer Primary_key=True) name = Column (String (32)) father = relationship ('Father') # create foreign key father_id = Column (Integer, ForeignKey (' father.id')) class Father (Base): _ _ tablename__ = 'father' id = Column (Integer, primary_key=True) name = Column (String (32) son = relationship (' Son') # son = relationship ('Son') Backref='Father') is equivalent to the above two relationship # generation tables Base.metadata.create_all (engine)

Add data to the table

Session = sessionmaker (bind=engine) session = Session () # add father's data F = Father (name='as') session.add (F) session.commit () # add son's data S1 = Son (name='Son1', father_id=1) S2 = Son (name='Son2', father_id=1) session.add_all ([S1 S2]) session.commit () # another way to add data F = session.query (Father). Filter_by (id=1). First () S3 = Son (name='Son3') # to add by append F.son is a list. If you do not use append, the corresponding values of the previous data will be deleted. F.son.append (S3) session.add (F) session.commit ()

Find all the sons through the father

Result = session.query (Father). Filter_by (name='as'). First () for n in result.son: print (n.name)

Find the father through the son

Result = session.query (Son). Filter_by (name='Son2'). First () print (result.father.name, result.name) # son = relationship ('Son', backref='Father') # print (result.father.name, result.name)

Join

Result = session.query (Father.name.label ('kkk'), Son.name.label (' ppp')) .join (Son) # label ('kkk') is equivalent to an alias, equal to asprint (result) > SELECT father.name AS kkk, son.name AS ppp FROM father JOIN son ON father.id = son.father_id in sql

Many-to-many instance

Add the following code to the Server class in the many-to-many code above:

G = relationship ("Group", secondary=ServerToGroup.__table__, backref='s') # secondary if a third table is added automatically

Then generate the database table.

Add data for groups and hosts

G1 = Group (name='G1', port=22) G2 = Group (name='G2', port=22) S1 = Server (hostname='Linux-node1') S2 = Server (hostname='Linux-node2') session.add_all ([G1, G2, S1, S2]) session.commit ()

Add associated data to the third table

GS1 = ServerToGroup (server_id=1, group_id=1) GS2 = ServerToGroup (server_id=2, group_id=2) session.add_all ([GS1, GS2]) session.commit ()

Add data through relationship

# get the host of ID=1 S = session.query (Server). Filter_by (id=1). First () # get all host groups G = session.query (Group). All () S.g = G# add data session.add_all ([S,]) # submit to the database session.commit ()

# Python full Stack Road # Sqlalchemy

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.