SQLAlchemy核心与Python中的PostgreSQL，连接.execute(..)错误。

huangapple
go评论90阅读模式
英文:

sqlalchemy core with postgresql in python, conneting.execute(..) error

问题

我正在学习在Python中使用PostgreSQL数据库的SQLAlchemy核心。

我尝试运行以下脚本，并收到以下错误消息：

  1. from sqlalchemy import create_engine  
  2. from sqlalchemy import Table, MetaData, String
  4. engine = create_engine('postgresql://postgres:123456@localhost:5432/red30')
  6. with engine.connect() as connection:
  7.     meta = MetaData(engine)  
  8.     sales_table = Table('sales', meta)
  10.     # 创建
  11.     insert_statement = sales_table.insert().values(order_num=1105911, 
  12.                                                 order_type='Retail', 
  13.                                                 cust_name='Syman Mapstone', 
  14.                                                 prod_number='EB521', 
  15.                                                 prod_name='Understanding Artificial Intelligence', 
  16.                                                 quantity=3, 
  17.                                                 price=19.5, 
  18.                                                 discount=0, 
  19.                                                 order_total=58.5)
  20.     connection.execute(insert_statement)
  22.     # 读取
  23.     select_statement = sales_table.select().limit(10)
  24.     result_set = connection.execute(select_statement)
  25.     for r in result_set:
  26.         print(r)
  28.     # 更新
  29.     update_statement = sales_table.update().where(sales_table.c.order_num==1105910).values(quantity=2, order_total=39)
  30.     connection.execute(update_statement)
  32.     # 确认更新：读取
  33.     reselect_statement = sales_table.select().where(sales_table.c.order_num==1105910)
  34.     updated_set = connection.execute(reselect_statement)
  35.     for u in updated_set:
  36.         print(u)
  38.     # 删除
  39.     delete_statement = sales_table.delete().where(sales_table.c.order_num==1105910)
  40.     connection.execute(delete_statement)
  42.     # 确认删除：读取
  43.     not_found_set = connection.execute(reselect_statement)
  44.     print(not_found_set.rowcount)

错误消息：

  1. (postgres-prac) E:\xfile\postgresql\postgres-prac>python postgres-sqlalchemy-core.py
  2. Traceback (most recent call last):
  3.   File "postgres-sqlalchemy-core.py", line 20, in <module>
  4.     connection.execute(insert_statement)
  5.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\engine\base.py", line 1414, in execute
  6.     return meth(
  7.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\elements.py", line 485, in _execute_on_connection
  8.     return connection._execute_clauseelement(
  9.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\engine\base.py", line 1630, in _execute_clauseelement
  10.     compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  11.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\elements.py", line 651, in _compile_w_cache
  12.     compiled_sql = self._compiler(
  13.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\elements.py", line 290, in _compiler
  14.     return dialect.statement_compiler(dialect, self, **kw)
  15.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compiler.py", line 1269, in __init__
  16.     Compiled.__init__(self, dialect, statement, **kwargs)
  17.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compiler.py", line 710, in __init__
  18.     self.string = self.process(self.statement, **compile_kwargs)
  19.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compiler.py", line 755, in process
  20.     return obj._compiler_dispatch(self, **kwargs)
  21.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\visitors.py", line 143, in _compiler_dispatch
  22.     return meth(self, **kw)  # type: ignore  # noqa: E501
  23.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compiler.py", line 5317, in visit_insert
  24.     crud_params_struct = crud._get_crud_params(
  25.   File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\crud.py", line 326, in _get_crud_params
  26.     raise exc.CompileError(
  27. sqlalchemy.exc.CompileError: Unconsumed column names: order_type, quantity, cust_name, discount, prod_number, price, order_total, order_num, prod_name

如有需要，我可以提供有关此错误的解释或建议。

英文:

I am learning sqlalchemy core with postgresql database in python.

I tried to run the following script and got this error message:

  1. from sqlalchemy import create_engine  
  2. from sqlalchemy import Table, MetaData, String
  4. engine = create_engine(&#39;postgresql://postgres:123456@localhost:5432/red30&#39;)
  6. with engine.connect() as connection:
  7.     meta = MetaData(engine)  
  8.     sales_table = Table(&#39;sales&#39;, meta)
  10.     # Create
  11.     insert_statement = sales_table.insert().values(order_num=1105911, 
  12.                                                 order_type=&#39;Retail&#39;, 
  13.                                                 cust_name=&#39;Syman Mapstone&#39;, 
  14.                                                 prod_number=&#39;EB521&#39;, 
  15.                                                 prod_name=&#39;Understanding Artificial Intelligence&#39;, 
  16.                                                 quantity=3, 
  17.                                                 price=19.5, 
  18.                                                 discount=0, 
  19.                                                 order_total=58.5)
  20.     connection.execute(insert_statement)
  22.     # Read
  23.     select_statement = sales_table.select().limit(10)
  24.     result_set = connection.execute(select_statement)
  25.     for r in result_set:
  26.         print(r)
  28.     # Update
  29.     update_statement = sales_table.update().where(sales_table.c.order_num==1105910).values(quantity=2, order_total=39)
  30.     connection.execute(update_statement)
  32.     # Confirm Update: Read
  33.     reselect_statement = sales_table.select().where(sales_table.c.order_num==1105910)
  34.     updated_set = connection.execute(reselect_statement)
  35.     for u in updated_set:
  36.         print(u)
  38.     # Delete
  39.     delete_statement = sales_table.delete().where(sales_table.c.order_num==1105910)
  40.     connection.execute(delete_statement)
  42.     # Confirm Delete: Read
  43.     not_found_set = connection.execute(reselect_statement)
  44.     print(not_found_set.rowcount)

error message:

  1. (postgres-prac) E:\xfile\postgresql\postgres-prac&gt;python postgres-sqlalchemy-core.py
  2. Traceback (most recent call last):
  3.   File &quot;postgres-sqlalchemy-core.py&quot;, line 20, in &lt;module&gt;
  4.     connection.execute(insert_statement)
  5.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\engine\ba
  6. se.py&quot;, line 1414, in execute
  7.     return meth(
  8.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\eleme
  9. nts.py&quot;, line 485, in _execute_on_connection
  10.     return connection._execute_clauseelement(
  11.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\engine\ba
  12. se.py&quot;, line 1630, in _execute_clauseelement
  13.     compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  14.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\eleme
  15. nts.py&quot;, line 651, in _compile_w_cache
  16.     compiled_sql = self._compiler(
  17.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\eleme
  18. nts.py&quot;, line 290, in _compiler
  19.     return dialect.statement_compiler(dialect, self, **kw)
  20.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compi
  21. ler.py&quot;, line 1269, in __init__
  22.     Compiled.__init__(self, dialect, statement, **kwargs)
  23.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compi
  24. ler.py&quot;, line 710, in __init__
  25.     self.string = self.process(self.statement, **compile_kwargs)
  26.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compi
  27. ler.py&quot;, line 755, in process
  28.     return obj._compiler_dispatch(self, **kwargs)
  29.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\visit
  30. ors.py&quot;, line 143, in _compiler_dispatch
  31.     return meth(self, **kw)  # type: ignore  # noqa: E501
  32.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compi
  33. ler.py&quot;, line 5317, in visit_insert
  34.     crud_params_struct = crud._get_crud_params(
  35.   File &quot;E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\crud.
  36. py&quot;, line 326, in _get_crud_params
  37.     raise exc.CompileError(
  38. sqlalchemy.exc.CompileError: Unconsumed column names: order_type, quantity, cust
  39. _name, discount, prod_number, price, order_total, order_num, prod_name

答案1

得分: 1

你将表定义为空表格：

  1. sales_table = Table('sales', meta)

所以，当尝试插入包含所有这些关键词的记录时，它们无法映射到列并且不会被消耗，因此会出现“未消耗的列名”错误。

你需要在创建Table时定义表格列。请参考以下来自文档的示例：

  1. from sqlalchemy import Table, Column, Integer, String
  3. user = Table(
  4.     "user",
  5.     metadata_obj,
  6.     Column("user_id", Integer, primary_key=True),
  7.     Column("user_name", String(16), nullable=False),
  8.     Column("email_address", String(60)),
  9.     Column("nickname", String(50), nullable=False),
  10. )
英文:

You define your table as an empty table:

  1. sales_table = Table(&#39;sales&#39;, meta)

So when trying to insert a record with all those keywords, they cannot be mapped to columns and do not get consumed, hence the Unconsumed column names error.

You need to define the table columns in your Table creation. See the following example from the docs:

  1. from sqlalchemy import Table, Column, Integer, String
  3. user = Table(
  4.     &quot;user&quot;,
  5.     metadata_obj,
  6.     Column(&quot;user_id&quot;, Integer, primary_key=True),
  7.     Column(&quot;user_name&quot;, String(16), nullable=False),
  8.     Column(&quot;email_address&quot;, String(60)),
  9.     Column(&quot;nickname&quot;, String(50), nullable=False),
  10. )

huangapple
  • 本文由 发表于 2023年2月16日 19:34:55
  • 转载请务必保留本文链接：https://go.coder-hub.com/75471680.html
  • postgresql
  • python
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定