SQLAlchemy(1) -- Python的SQLAlchemy和ORM
2017-10-02 14:55 abce 阅读(470) 评论(0) 编辑 收藏 举报Python的SQLAlchemy和ORM(object-relational mapping:对象关系映射)
web编程中有一项常规任务就是创建一个有效的后台数据库。以前,程序员是通过写sql语句,发送到数据库引擎,解析并返回一个记录的数组。现在,程序员可以写ORM程序来取代之前那种不灵活、难以维护的冗长、易出错的sql语句。
ORM是面向对象编程语言中用来在不兼容的类型系统(incompatible type systems)之间转换数据的一种编程技术。通常在OO语言中的类型系统,比如python包含的类型是非标量的,也就是说这些类型不能使用原始的类型比如(integer、string)来表达。比如,一个person对象可能含有一个address对象的列表,和一个phonenumber对象的列表。同理,一个address对象可能包含一个postcode对象,一个streetname对象和一个streetnumber对象。尽管简单对象比如postcode、streetname可以用字符串来表示,但是更复杂的对象比如address、person就不能仅仅用字符串、整形数字来表示了。此外,这些复杂的对象还会具有实例或类方法,这些就更不能简单用字符串或整形数字来表示了。
为了处理这些复杂的对象管理问题,人们设计了ORM。上面我们的示例可以用一个ORM系统表示出来:设计一个person类、address类、phonenumber类,每个类映射到数据库中的一张表。这样就不再需要编写各种冗长的数据接口程序,而可以更加专注于系统的逻辑设计。
python中写数据库的代码(旧的方式)
使用pymysql创建两张表:
编写python脚本pymysql_ex.py并执行:
$ python pymysql_ex.py
#!/usr/bin/env python # _*_ coding:utf-8 _*_ import pymysql #创建链接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='abce', passwd='abce', db='abce', charset='utf8') #创建游标 c = conn.cursor() #执行sql建表,插入内容 c.execute(''' create table person (id integer primary key, name varchar(250) not null) ''') c.execute(''' create table address (id integer primary key, street_name varchar(250), street_number varchar(250), post_code varchar(250) 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()
编写脚本pymysql_q.py查看数据库表的内容:
#!/usr/bin/env python # _*_ coding:utf-8 _*_ import pymysql #创建连接 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),)
例子中我们使pymysql连接提交对数据库的修改,并使用pymysql的游标来执行各种sql语句。尽管这些sql语句完成了相关的工作,但是维护sql语句的本身也不是件容易的事。下面,我们来看看sqlalchemy在Python得类和表之间是如何映射的。
Python's SQLAlchemy and Declarative
写SQLAlchemy代码有三个重要组件:
--数据库中的表
--mapper:将python的类映射到数据库中的表
--类对象,定义如何将数据库的记录映射到一个python对象
不需要在不同的地方写表、mapper、class的代码,SQLAlchemy的declarative支持将表、mapper和类对象定义到一个类中。
下面创建一个declarative (sqlalchemy_declarative.py)
#!/usr/bin/env python # _*_ coding:utf-8 _*_ import pymysql import os import sys from sqlalchemy import Column, ForeignKey, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship from sqlalchemy import create_engine Base = declarative_base() class Person(Base): __tablename__ = 'person' # Here we define columns for the table person # Notice that each column is also a normal Python instance attribute. id = Column(Integer, primary_key=True) name = Column(String(250), 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(250)) street_number = Column(String(250)) post_code = Column(String(250), nullable=False) person_id = Column(Integer, ForeignKey('person.id')) person = relationship(Person) # 连接数据库采用pymysq模块做映射,后面参数是最大连接数5 engine = create_engine('mysql+pymysql://abce:abce@127.0.0.1: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)
执行脚本,就会创建了相应的数据库表
$ python sqlalchemy_declarative.py
接下来,我们插入一些数据(sqlalchemy_insert.py)
#!/usr/bin/env python # _*_ coding:utf-8 _*_ import pymysql from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy_declarative import Address, Base, Person engine = create_engine('mysql+pymysql://abce:abce@127.0.0.1:3306/abce?charset=utf8') # Bind the engine to the metadata of the Base class so that the # declaratives can be accessed through a DBSession instance Base.metadata.bind = engine DBSession = sessionmaker(bind=engine) # A DBSession() instance establishes all conversations with the database # and represents a "staging zone" for all the objects loaded into the # database session object. Any change made against the objects in the # session won't be persisted into the database until you call # session.commit(). If you're not happy about the changes, you can # revert all of them back to the last commit by calling # session.rollback() session = DBSession() # Insert a Person in the person table new_person = Person(name='new person') session.add(new_person) session.commit() # Insert an Address in the address table new_address = Address(post_code='00000', person=new_person) session.add(new_address) session.commit()
从数据库后台可以直接看到数据:
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>
当然我们得重点是从python中查看插入的数据:
>>> from sqlalchemy_declarative import Person, Base, Address >>> from sqlalchemy import create_engine >>> engine = create_engine('mysql+pymysql://abce:abce@172.0.0.1: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 all Persons in the database >>> session.query(Person).all() [<sqlalchemy_declarative.Person object at 0x21c7390>] >>> >>> # Return the first Person from all Persons in the database >>> person = session.query(Person).first() >>> person.name u'new person' >>> >>> # Find all Address whose person field is pointing to the person object >>> session.query(Address).filter(Address.person == person).all() [<sqlalchemy_declarative.Address object at 0x22b08d0>] >>> >>> # Retrieve one Address whose person field is point to the person object >>> session.query(Address).filter(Address.person == person).one() <sqlalchemy_declarative.Address object at 0x22b08d0> >>> address = session.query(Address).filter(Address.person == person).one() >>> address.post_code u'00000'
总结
以上就是如何使用sqlalchemy的declaratives来编写数据库代码。和传统的sql语句相比,显得更面向对象、更易于理解和维护。
参考原文地址:http://pythoncentral.io/introductory-tutorial-python-sqlalchemy/