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

在开发中遇到了一个奇葩的问题,设计的数据库其中有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

结果正常!

 

新开公众号“码家村”,欢迎关注

 

SQLAlchemyColumn定义常用的属性有: 1. type:指定列的数据类型,常用的数据类型有String、Integer、Boolean、DateTime等。例如: ```python from sqlalchemy import Column, String class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) ``` 2. primary_key:指定列是否为主键。例如: ```python from sqlalchemy import Column, Integer, String class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) ``` 3. unique:指定列的值是否唯一。例如: ```python from sqlalchemy import Column, Integer, String class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50), unique=True) ``` 4. nullable:指定列是否可以为NULL。例如: ```python from sqlalchemy import Column, Integer, String class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50), nullable=False) ``` 5. default:指定列的默认值。例如: ```python from sqlalchemy import Column, Integer, String, DateTime, func class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50), nullable=False) created_at = Column(DateTime, default=func.now()) ``` 6. index:指定列是否创建索引。例如: ```python from sqlalchemy import Column, Integer, String, Index class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) email = Column(String(50), unique=True, index=True) idx_name_email = Index('idx_name_email', name, email) ``` 7. server_default:指定数据库端默认值。例如: ```python from sqlalchemy import Column, Integer, String class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) status = Column(Integer, server_default='0') ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值