On SQLAlchemy ORM of python, sqlalchemyorm

Source: Internet
Author: User

On SQLAlchemy ORM of python, sqlalchemyorm

Preface:SQLAlchmey was learned during the summer vacation. After completing the course, I did not write my blog in time. This blog mainly introduces SQLAlchemy and basic operations. After writing this article, you will be free to create a bastion host project. If you are interested, please refer to the python database (mysql) operation. In the next blog, I will write an article about the Web framework and django basics ~~

I. Introduction to ORM

The full name of orm is object relational ing, which is an object ing program. In simple words, we use object-oriented programs like python, but the databases we use are relational, to ensure consistent usage habits, orm is used to establish a ing relationship between the object model of the programming language and the relational model of the database, in this way, you can directly use the object model of the programming language to operate the database, instead of using the SQL language directly.

Advantages of orm:

  • Hiding data access details, "closed" Universal Database interaction, and the core of ORM. It makes the interaction between our general databases easy and easy, and does not need to consider damn SQL statements at all. Rapid development.
  • ORM makes it easy to construct a solid data structure.

Disadvantages:

  • Inevitably, automation means ing and association management at the cost of sacrificing performance (early on, this is what all people do not like ORM ). Currently, various ORM frameworks are trying to use various methods to reduce this (LazyLoad, Cache). The effect is quite remarkable.

 

Ii. SQLAlchemy framework and database API

In Python, the most famous ORM framework is SQLAlchemy. Users include openstack \ Dropbox and other well-known companies or applications, the main user list http://www.sqlalchemy.org/organizations.html#openstack

You need to map tables in the database into classes before calling them through objects. SQLAlchemy not only supports MYSQL, but also Oracle.

Dialect is used to communicate with data APIs. Different database APIs are called based on different configuration files to perform database operations:

MySQL-Python mysql + mysqldb: // <user >:< password >@< host> [: <port>]/<dbname> pymysql mysql + pymysql: // <username >:< password >@< host>/<dbname> [? <Options>] MySQL-Connector mysql + mysqlconnector: // <user >:< password >@< host> [: <port>]/<dbname> cx_Oracle oracle + cx_oracle: // user: pass @ host: port/dbname [? Key = value & key = value...] For more information, see: http://docs.sqlalchemy.org/en/latest/dialects/index.html

Install SQLAlchemy:

pip install SQLAlchemy

 

3. connect to and query the database
1 from sqlalchemy import create_engine2 3 # connect to the database and generate the engine object; the maximum number of connections is 5 4 engine = create_engine ("mysql + pymysql: // root: root@127.0.0.1: 3306/zcl ", max_overflow = 5) 5 print (engine) # Engine (mysql + pymysql: // root: *** @ 127.0.0.1: 3306/zcl) 6 result = engine.exe cute ('select * from students ') # automatically commit7 print (result. fetchall ())

Output:

Engine(mysql+pymysql://root:***@127.0.0.1:3306/zcl)[(1, 'zcl', 'man', 22, '15622341234', None), (2, 'alex', 'man', 30, '15622341235', None), (5, 'Jack', 'man', 25, '1351234', 'CN'), (6, 'Mary', 'female', 18, '1341234', 'USA'), (10, 'Jack', 'man', 25, '1351234', 'CN'), (11, 'Jack2', 'man', 25, '1351234', 'CN'), (12, 'Mary', 'female', 18, '1341234', 'USA'), (13, 'cjy', 'man', 18, '1562234', 'USA'), (14, 'cjy2', 'man', 18, '1562235', 'USA'), (15, 'cjy3', 'man', 18, '1562235', 'USA'), (16, 'cjy4', 'man', 18, '1562235', 'USA'), (17, 'cjy5', 'man', 18, '1562235', 'USA')]
View Code

 

4. Create a table

Create a user and color table: when creating a table, you must bind it to the MetaData instance.

1 from sqlalchemy import create_engine, \ 2 Table, Column, Integer, String, MetaData, ForeignKey 3 4 metadata = MetaData () # It is equivalent to an instance with a parent class 5 6 user = Table ('user', metadata, # It is equivalent to letting the Table inherit the metadata Class 7 Column ('id', Integer, primary_key = True ), 8 Column ('name', String (20), 9) 10 11 color = Table ('color', metadata, # Table name color12 Column ('id', Integer, primary_key = True), 13 Column ('name', String (20), 14) 15 engine = create_engine ("mysql + pymysql: // root: root @ localhost: 3306/zcl ", max_overflow = 5) 16 17 metadata. create_all (engine) # table already bound to metadate

View the created table:

 

5. add, delete, modify, and query

First, let's take a look at the addition, deletion, modification, and query of native SQL statements:

1 from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey, select 2 3 metadata = MetaData () 4 5 user = Table ('user', metadata, 6 Column ('id', Integer, primary_key = True), 7 Column ('name', String (20), 8) 9 10 color = Table ('color ', metadata, 11 Column ('id', Integer, primary_key = True), 12 Column ('name', String (20), 13) 14 engine = create_engine ("mysql + pymysql: // root: root@127.0.0.1: 3306/zcl", max_overflow = 5) 15 16 conn = engine. connect () # create a cursor, current instance status 17 18 # create an SQL statement, INSERT INTO "user" (id, name) VALUES (: id,: name) 19 # ID can be omitted. The default value is auto-increment 20 # conn.exe cute (user. insert (), {'id': 1, 'name': 'zcl '}) 21 # conn. close () 22 23 # SQL = user. insert (). values (name = 'wu ')#Insert24 # conn.exe cute (SQL) 25 # conn. close () 26 27 #DeleteThe row whose ID number is greater than 1 can also be where (user. c. name = "zcl") 28 # SQL = user. delete (). where (user. c. id> 1) 29 # conn.exe cute (SQL) 30 # conn. close () 31 32 # Change name = "wuu" to "name =" ed "33 # SQL = user. update (). where (user. c. name = 'wuu '). values (name = 'ed') 34 # conn.exe cute (SQL) 35 # conn. close () 36 37 #QuerySQL = user cannot be written below. select... 38 # SQL = select ([user,]) # [(1, 'zcl '), (9, 'ed'), (10, 'ed')] 39 # SQL = select ([user. c. id,]) # [(1,), (9,), (10,)] 40 SQL = select ([user. c. name, color. c. name]). where (user. c. id = color. c. id) 41 # SQL = select ([user. c. name]). order_by (user. c. name) 42 # SQL = user. select ([user]). group_by (user. c. name) 43 44 result = conn.exe cute (SQL) 45 print (result. fetchall () 46 conn. close ()

 

Add, delete, modify, and query through SQLAlchemy (important ):

1 from sqlalchemy import create_engine 2 from sqlalchemy. ext. declarative import declarative_base 3 from sqlalchemy import Column, Integer, String 4 from sqlalchemy. orm import sessionmaker 5 6 Base = declarative_base () # generate a SqlORM Base class (metadata has been encapsulated) 7 # echo = True: You can view the table creation process. 8 engine = create_engine ("mysql + pymysql: // root: root @ localhost: 3306/zcl", echo = True) 9 10 class Host (Base): 11 _ tablename _ = 'hosts' # table name host12 id = Column (Integer, primary_key = True, autoincrement = True) 13 hostname = Column (String (64), unique = True, nullable = False) 14 ip_addr = Column (String (128), unique = True, nullable = False) 15 port = Column (Integer, default = 22) 16 17 18 Base. metadata. create_all (engine) # create all table structures 19 20 if _ name _ = '_ main _': 21 # create a session sessionclass with the database. Note, here, the session is returned as a class, not instance 22 SessionCls = sessionmaker (bind = engine) 23 session = SessionCls () # connected instance 24 #PreparationInsert data 25 h1 = Host (hostname = 'localhost', ip_addr = '2017. 0.0.1 ') # instantiate (not created) 26 h2 = Host (hostname = 'ubuntu', ip_addr = '2017. 168.2.243 ', port = 20000) 27 28 # session. add (h1) # You can also use the following batch processing 29 # session. add_all ([h1, h2]) 30 # h2.hostname = 'ubuntu _ test' # As long as it is not submitted, there is no problem with modification at this time 31 32 # query data, returns an object 33 obj = session. query (Host ). filter (Host. hostname = "localhost "). first () 34 print ("-->", obj) 35 # [<__ main __. hostobjectat0x00000000048DC0B8>] if it is. all () 36 # <__ main __. hostobjectat0x000000000493C208> if it is. first () 37 38 # if used. all (), the error AttributeError: 'LIST' objecthasnoattribute 'hostname' 39 # obj. hostname = "localhost_1" # change the host name to localhost_140 41 session. delete (obj) # delete row 42 43 session. commit () # submit

 

Operation Result:

 

6. Foreign key Association

Create and associate the host table hosts with the group table group. That is, a group can correspond to multiple hosts:

1 from sqlalchemy import create_engine 2 from sqlalchemy. ext. declarative import declarative_base 3 from sqlalchemy import Column, Integer, String, ForeignKey 4 from sqlalchemy. orm import sessionmaker, relationship 5 6 Base = declarative_base () # generate a SqlORM Base class (metadata has been closed) 7 # echo = True: You can view the table creation process. 8 engine = create_engine ("mysql + pymysql: // root: root @ localhost: 3306/zcl", echo = True) 9 10 class Host (Base): 11 _ tablename _ = 'hosts' # Table Name 12 id = Column (Integer, primary_key = True, autoincrement = True) # default auto-increment 13 hostname = Column (String (64), unique = True, nullable = False) 14 ip_addr = Column (String (128), unique = True, nullable = False) 15 port = Column (Integer, default = 22) 16 # foreign key Association, host and group name Association, one group corresponds to multiple hosts 17 group_id = Column (Integer, foreignKey ("group. id ") 18 19 20 class Group (Base): 21 _ tablename _ =" group "22 id = Column (Integer, primary_key = True) 23 name = Column (String (64), unique = True, nullable = False) 24 25 26 Base. metadata. create_all (engine) # create all table structures 27 28 if _ name _ = '_ main _': 29 # create a session class with the database. Note, the class returned to the session is not instance 30 SessionCls = sessionmaker (bind = engine) 31 session = SessionCls () # connected instance 32 33 session. commit () # submit

View results:

View the structure of the newly created group table or query from the group table, you will find desc group; select * from group will all be exposed wrong !! (Why is this error caused by some association between the group and the database? For example: group by... I guess)

Solution: desc zcl. group; select * from zcl. group; (zcl indicates the database name)

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.