Python工具箱系列(三十四)

天蓝色海豚

SQLAlchemy是著名的ORM(Object Relational Mapping-对象关系映射)框架。其主要作用是在编程中，把面向对象的概念跟数据库中表的概念对应起来。对许多语言（例如JAVA/PYTHON）来说就是定义一个对象，并且这个对象对应着一张数据库的表。而这个对象的实例，就对应着表中的一条记录。
其整体思路如下图所示：

其中类、对象与属性与数据库相关内容的对应关系如下图所示：

ORM的优点：
 

• 数据模型与代码统一定义，更新与维护简单，代码高度重用一致。
  
• ORM有现成的工具，很多功能都可以自动完成，比如表格增删、预处理、事务等。
  
• 基于ORM的业务代码比较简单，代码量少，语义性好，容易理解。
  
• 你不必编写性能不佳的SQL。
  

ORM的缺点：
 

• ORM库多层封装，实现巧妙，需要花很多精力学习和设置。
  
• 对于复杂的查询，ORM要么是无法表达，要么是性能不如原生的SQL。
  
• ORM抽象掉了数据库层，开发者无法了解底层的数据库操作，也无法定制一些特殊的SQL。
  

 
 
 从整体上看，ORM节省了开发时间，减少了代码错误的可能性，同时能够方便地在多个数据库间灵活迁移，还是非常值得使用。而在python语言中，SQLAlchemy是著名的ORM框架之一，它的整体架构如下图所示：

从图中可以看出，SQLAIchemy是分层架构，由Core以及ORM两部分组成。其中，Core完成了与数据库操作的各类封闭，是相对低层的。而ORM层则利用Core层的能力进行更宏观的操作。因此，在一段python代码中,使用Core与ORM层同时来操作数据库也是可行的，并不矛盾与冲突。
下面先从最基本的表格创建做起。非ORM编程中，表格的创建无非两个途径：
●基于DBMS本身提供的CLI/GUI界面，发出DDL语句进行数据库/表格本身的增删改查。
●使用语言连接数据库后，发出命令来对数据库/表格进行增删改查。
而由于每种数据库都有自己的方言，所以命令语句各有差异，需要不断地调整。而使用SQLAlchemy则实现了代码统一。例如以下代码在mssql以及mysql上创建表格，并且可以查询表格的元数据，以及插入数据后的查询。

1. from sqlalchemy import (Column, Integer, MetaData, String, Table,
2.                         create_engine, text, Float, DateTime, ForeignKey)
3. from sqlalchemy_utils.functions import create_database, database_exists
5. configure_pg = {"user": "postgres",
6.                 'password': '88488848',
7.                 'dns': 'dbserver.home',
8.                 "port": 5432,
9.                 'prefix': 'postgresql+psycopg2',
10.                 'postfix': ''
11.                 }
13. configure_mssql = {"user": "sa",
14.                    'password': '88488848',
15.                    'dns': 'dbserver.home',
16.                    "port": 1433,
17.                    'prefix': 'mssql+pymssql',
18.                    'postfix': '?charset=utf8'
19.                    }
21. configure_mysql = {"user": "root",
22.                    'password': '88488848',
23.                    'dns': 'dbserver.home',
24.                    "port": 3306,
25.                    'prefix': 'mysql+mysqlconnector',
26.                    'postfix': ''
27.                    }
29. config = {'mssql': configure_mssql,
30.           'mysql': configure_mysql, 'postgresql': configure_pg}
32. database_name = 'testdb'
33. table_sensor_location = "sensor_location"
34. table_sensor_data = "sensor_data"
37. def linkdb(targetstr):
38.     """
39.     连接不同的数据库
41.     Args:
42.         targetstr (string): 数据库名称
44.     Returns:
45.         engine: 用于后续的数据库连接
46.     """
47.     if targetstr in config.keys():
48.         item = config[targetstr]
49.         connectstring = f"{item['prefix']}://{item['user']}:{item['password']}@{item['dns']}:{item['port']}/{database_name}{item['postfix']}"
50.         engine = create_engine(connectstring, echo=True, future=True)
52.     # 如果数据库不存在，则创建之
53.     if not database_exists(engine.url):
54.         create_database(engine.url)
56.     # 做一个测试，不针对任何表
57.     with engine.connect() as conn:
58.         result = conn.execute(text("select 'hello world'"))
59.         print(result.all())
60.     return engine
63. def createtbs(connector):
64.     """"
65.     创建数据库中的2张表。用于保存传感器数据与传感器本身的信息
66.     """
67.     metadata_obj = MetaData()
69.     # 描述传感器的表
70.     sensor_location_tb = Table(
71.         table_sensor_location,
72.         metadata_obj,
73.         Column('id', Integer, primary_key=True, autoincrement=False),
74.         Column('location', String(30), nullable=False)
75.     )
77.     # 保存传感器数据的表
78.     sensor_data_tb = Table(
79.         table_sensor_data,
80.         metadata_obj,
81.         Column('id', Integer, primary_key=True, autoincrement=False),
82.         Column('sensor_id', ForeignKey(
83.             f'{table_sensor_location}.id'), nullable=False),
84.         Column('area', String(30)),
85.         Column('pm25', Float),
86.         Column('timestamp', DateTime)
87.     )
88.     print(sensor_data_tb.compile())
90.     # 创建并返回表
91.     metadata_obj.create_all(connector)
92.     return sensor_data_tb, sensor_location_tb
95. def tableinfo(connector, tablename):
96.     """
97.     获得指定表名的相关元数据信息
99.     Args:
100.         connector (engine): 数据库连接器
101.         tablename (string): 要查询的表名
102.     """
103.     metadata_obj = MetaData()
104.     some_table = Table(tablename, metadata_obj, autoload_with=connector)
105.     print([c.name for c in some_table.columns])
108. def gensonsorinfo(connector):
109.     with connector.connect() as conn:
110.         conn.execute(text(f"INSERT INTO {table_sensor_location} (id, location) VALUES (:x, :y)"),
111.                      [{"x": 1, "y": '1号楼'}, {"x": 2, "y": '2号楼'}])
113.         conn.commit()
115.         result = conn.execute(
116.             text(f"SELECT id, location FROM {table_sensor_location}"))
117.         for x, y in result:
118.             print(f"id: {x}  location: {y}")
121. # 依次连接多个数据库。从而验证代码的一致性
122. for dbname in config.keys():
123.     con = linkdb(dbname)
124.     createtbs(con)
125.     tableinfo(con, table_sensor_data)
126.     tableinfo(con, table_sensor_location)
127.     gensonsorinfo(con)

复制代码

从代码可以看出，可以用统一的访问方式来操作mssql/mysql/postgresql三种数据库。而且，以上方式与前文中的基于游标的写法类似。
 

来源:https://www.cnblogs.com/shanxihualu/p/17440033.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！