关于sqlalchemy中键值以双下划线开头导致Unknown column错误的问题

最新推荐文章于 2024-07-14 16:22:31 发布

魔笛手CTO

最新推荐文章于 2024-07-14 16:22:31 发布

阅读量1.4k

点赞数

分类专栏： Python 文章标签： sqlalchemy 双下划线 column 错误 sqlalchemy.exc.ProgrammingErro

本文链接：https://blog.csdn.net/afterlake/article/details/79945326

版权

Python 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

在开发中遇到了一个奇葩的问题，设计的数据库其中有Column以双下划线开头，导致在运行中出现错误。

现在以例子来复现并解决错误。

首先创建数据表

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    __name VARCHAR(16),
    age INT
);

该表为包含主键id，和另外两个column（__name、age）的student数据表，其中一个column的键值以双下划线开头。

mysql> DESC student;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| __name | varchar(16) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

现在我们向其中插入两条数据后查看数据表

mysql> SELECT * FROM student;
+----+--------+------+
| id | __name | age  |
+----+--------+------+
|  1 | zhang3 |   12 |
|  2 | wang2  |   10 |
+----+--------+------+
2 rows in set (0.00 sec)

这个时候我们就可以创建sql.py文件通过sqlalchemy连接我们的数据库

from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class Student(Base):
    # 表的名字:
    __tablename__ = 'student'

    # 表的结构:
    id = Column(Integer, primary_key=True, autoincrement=True)
    __name = Column(String(20))
    age = Column(Integer)

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

# 创建session对象并查询
session = DBSession()
students = session.query(Student).filter().all()

print(students)

运行之后我们会发现出错了

Traceback (most recent call last):
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "C:\Python\Python36\lib\site-packages\mysql\connector\cursor.py", line 566, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python\Python36\lib\site-packages\mysql\connector\connection.py", line 530, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python\Python36\lib\site-packages\mysql\connector\connection.py", line 432, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'student._Student__name' in 'field list'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/A/Desktop/db.py", line 25, in <module>
    students = session.query(Student).filter().all()
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\orm\query.py", line 2737, in all
    return list(self)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\orm\query.py", line 2889, in __iter__
    return self._execute_and_instances(context)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\orm\query.py", line 2912, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "C:\Python\Python36\lib\site-packages\mysql\connector\cursor.py", line 566, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python\Python36\lib\site-packages\mysql\connector\connection.py", line 530, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python\Python36\lib\site-packages\mysql\connector\connection.py", line 432, in _handle_result
    raise errors.get_exception(packet)
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1054 (42S22): Unknown column 'student._Student__name' in 'field list' [SQL: 'SELECT student.id AS student_id, student.`_Student__name` AS `student__Student__name`, student.age AS student_age \nFROM student'] (Background on this error at: http://sqlalche.me/e/f405)

检查错误，将会发现Unknown column 'student._Student__name'

明明定义的column是student.__name，为什变成了student._Student__name？

这就要从python的类说起了，python的类并没有关于私有变量的定义，但是有个近似私有变量的处理。

详细请查看python文档（关于私有变量的说明）

这里节选一段

>> Any identifier of the form__spam (at least two leading underscores, at most one trailing underscore)is textually replaced with _classname__spam, where classname is thecurrent class name with leading underscore(s) stripped

是说类的定义中任何以双下划线开头的变量都将被重命名为 _类名__变量名 的形式

所以回到sqlalchemy的问题，就会发现student.__name被重命名为了student._Student__name，而数据表中并没有_Student__name，导致查询出错。

在不能够改变数据库的前提下如何修复这个错误呢？其实很简单，只要将变量放在类定义外面就好了。

重新修改一下sql.py

from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class Student(Base):
    # 表的名字:
    __tablename__ = 'student'
    # 表的结构:
    id = Column(Integer, primary_key=True, autoincrement=True)


# 修该此处,将带有双下划线的变量放在类外
Student.__name = Column(String(20))
Student.age = Column(Integer)

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

# 创建session对象并查询
session = DBSession()
students = session.query(Student).filter().all()

for student in students:
    print(student.__name)

此时再次运行代码