python：mysql之UNION操作

最新推荐文章于 2023-11-03 16:36:19 发布

番茄牛腩不吃番茄

最新推荐文章于 2023-11-03 16:36:19 发布

阅读量620

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/LIFENG0402/article/details/118610123

版权

数据库专栏收录该内容

15 篇文章 1 订阅

订阅专栏

接下来。装逼开始....

mysql> select * from test_user;
+----+--------+-------------+----------+-----------+
| id | name   | mobile      | address  | country   |
+----+--------+-------------+----------+-----------+
|  1 | JAVA   | 18847474541 | Beijing  | China     |
|  2 | Python | 17748484242 | Nanjing  | Singapore |
|  3 | Php    | 17748484246 | Beijing  | China     |
|  4 | C#     | 17748484141 | Shanghai | Singapore |
|  5 | Go     | 18847474142 | Shanghai | China     |
|  6 | Swift  | 17748484142 | Beijing  | China     |
+----+--------+-------------+----------+-----------+
6 rows in set (0.00 sec)

mysql> select * from test_user_copy;
+----+--------+-------------+----------+-----------+
| id | name   | mobile      | address  | country   |
+----+--------+-------------+----------+-----------+
|  1 | JAVA   | 18847474541 | Beijing  | China     |
|  2 | Python | 17748484242 | Nanjing  | Singapore |
|  3 | SQL    | 17748484246 | Beijing  | China     |
|  4 | R      | 17748484141 | Shanghai | Singapore |
+----+--------+-------------+----------+-----------+
4 rows in set (0.00 sec)

上述两张表，表结构是一致的，里面存储的数据有一致的，也不一致的；下面开始使用上述表的数据执行SQL中含UNION语句…

–

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

UNION 实例：

mysql> select name, country from test_user
    -> union
    -> select name, country from test_user_copy
    -> order by country;
+--------+-----------+
| name   | country   |
+--------+-----------+
| Swift  | China     |
| SQL    | China     |
| JAVA   | China     |
| Php    | China     |
| Go     | China     |
| R      | Singapore |
| Python | Singapore |
| C#     | Singapore |
+--------+-----------+
8 rows in set (0.00 sec)

–
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

UNION ALL 实例：

mysql> select name, country from test_user
    -> union all
    -> select name, country from test_user_copy
    -> order by country;
+--------+-----------+
| name   | country   |
+--------+-----------+
| Swift  | China     |
| JAVA   | China     |
| SQL    | China     |
| JAVA   | China     |
| Php    | China     |
| Go     | China     |
| Python | Singapore |
| R      | Singapore |
| Python | Singapore |
| C#     | Singapore |
+--------+-----------+
10 rows in set (0.00 sec)

从上述的示例中可以总结到：UNION 操作符用于合并两个或多个 SELECT 语句的结果集，并把数据展示出来…

–

如果test_user_copy表比test_user表多出一列数据，那该怎么处理呢？

mysql> select * from test_user_copy;
+----+--------+-------------+----------+-----------+---------------------+
| id | name   | mobile      | address  | country   | school              |
+----+--------+-------------+----------+-----------+---------------------+
|  1 | JAVA   | 18847474541 | Beijing  | China     | Beijing University  |
|  2 | Python | 17748484242 | Nanjing  | Singapore | Nanjing University  |
|  3 | SQL    | 17748484246 | Beijing  | China     | Beijing University  |
|  4 | R      | 17748484141 | Shanghai | Singapore | Shanghai University |
+----+--------+-------------+----------+-----------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from test_user;
+----+--------+-------------+----------+-----------+
| id | name   | mobile      | address  | country   |
+----+--------+-------------+----------+-----------+
|  1 | JAVA   | 18847474541 | Beijing  | China     |
|  2 | Python | 17748484242 | Nanjing  | Singapore |
|  3 | Php    | 17748484246 | Beijing  | China     |
|  4 | C#     | 17748484141 | Shanghai | Singapore |
|  5 | Go     | 18847474142 | Shanghai | China     |
|  6 | Swift  | 17748484142 | Beijing  | China     |
+----+--------+-------------+----------+-----------+
6 rows in set (0.00 sec)

–
python脚本执行UNION语句：

import pymysql


class MysqlData:
    def __init__(self, host="localhost", user="root", password="123456", database="testing", port=3306):
        # 连接数据库
        self.connection = pymysql.connect(host=host, user=user, password=password, database=database, port=port,
                                          charset='utf8', cursorclass=pymysql.cursors.DictCursor)

    def modify_data(self, sql, args=None):
        """
        Args:
            sql:    sql语句
            args:   接收格式化参数

        Returns:
        """
        try:
            with self.connection:
                self.connection.ping(reconnect=True)
                with self.connection.cursor() as cursor:
                    # 执行sql语句
                    cursor.execute(sql, args=args)
                # 提交sql语句
                self.connection.commit()
        except pymysql.err.MySQLError as _error:
            raise _error

    def get_data(self, sql, args=None, is_data=False):
        """
        Args:
            sql:        sql语句
            args:       接收格式化参数
            is_data:    判断获取多条数据还是单个数据，默认获取单个数据

        Returns:
        """
        try:
            with self.connection:
                self.connection.ping(reconnect=True)
                with self.connection.cursor() as cursor:
                    # 执行sql语句
                    cursor.execute(sql, args=args)

                    # 返回查询出的数据信息
                    return cursor.fetchall() if is_data else cursor.fetchone()

        except pymysql.err.MySQLError as _error:
            raise _error


if __name__ == '__main__':
    # 初始化类，创建对象
    info = MysqlData()

    # 调用查询方法并打印出来
    print(info.get_data("SELECT * FROM test_user UNION SELECT * FROM test_user_copy"))

Traceback (most recent call last):
  File "F:/project_gitee/Test/pythonScripts/python_mysql.py", line 63, in <module>
    print(info.get_data("SELECT * FROM test_user UNION SELECT * FROM test_user_copy"))
  File "F:/project_gitee/Test/pythonScripts/python_mysql.py", line 53, in get_data
    raise _error
  File "F:/project_gitee/Test/pythonScripts/python_mysql.py", line 47, in get_data
    cursor.execute(sql, args=args)
  File "D:\Python\Python37\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "D:\Python\Python37\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
    packet.check_error()
  File "D:\Python\Python37\lib\site-packages\pymysql\protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "D:\Python\Python37\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1222, 'The used SELECT statements have a different number of columns')

Process finished with exit code 1

然后就报错了：The used SELECT statements have a different number of columns

翻译过来就是：所使用的SELECT语句有不同数量的列

由此得到结论是：UNION 内部的 SELECT 语句必须拥有相同数量的列，也尽可能的保证数据类型也是一致的…

当然，上面是查询两张表的全部数据，但是如果指定两张表相同的属性去查询就不会报错了，UNION中也是允许跟随WHERE子句的…

–

python脚本执行带有 WHERE 子句的 UNION ALL 语句：

if __name__ == '__main__':
    # 初始化类，创建对象
    info = MysqlData()

    # 调用查询方法并打印出来
    results = info.get_data("""
            SELECT
                country, name
            FROM
                test_user_copy
            WHERE
                country = 'China'
            UNION ALL
                SELECT
                    country, name
                FROM
                    test_user_copy
                WHERE
                    country = 'China'
                ORDER BY
                    country 
    """, is_data=True)
    for i in results:
        print(i)

{'country': 'China', 'name': 'JAVA'}
{'country': 'China', 'name': 'SQL'}
{'country': 'China', 'name': 'JAVA'}
{'country': 'China', 'name': 'SQL'}

Process finished with exit code 0

至此，UNION操作查询完成…

以上总结或许能帮助到你，或许帮助不到你，但还是希望能帮助到你，如有疑问、歧义，直接私信留言会及时修正发布；感觉还不错记得点赞呦，谢谢！

未完，待续…

一直都在努力，希望您也是！

微信搜索公众号：就用python

番茄牛腩不吃番茄

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
python：mysql之UNION操作

接下来。装逼开始....mysql> select * from test_user;+----+--------+-------------+----------+-----------+| id | name | mobile | address | country |+----+--------+-------------+----------+-----------+| 1 | JAVA | 18847474541 | Beijing ...
复制链接

扫一扫

专栏目录