在开发中遇到了一个奇葩的问题,设计的数据库其中有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)
此时再次运行代码
zhang3
wang2
结果正常!
新开公众号“码家村”,欢迎关注