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

I am learning sqlalchemy core with postgresql database in python.

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

from sqlalchemy import create_engine  
from sqlalchemy import Table, MetaData, String

engine = create_engine('postgresql://postgres:123456@localhost:5432/red30')

with engine.connect() as connection:
    meta = MetaData(engine)  
    sales_table = Table('sales', meta)

    # Create
    insert_statement = sales_table.insert().values(order_num=1105911, 
                                                order_type='Retail', 
                                                cust_name='Syman Mapstone', 
                                                prod_number='EB521', 
                                                prod_name='Understanding Artificial Intelligence', 
                                                quantity=3, 
                                                price=19.5, 
                                                discount=0, 
                                                order_total=58.5)
    connection.execute(insert_statement)

    # Read
    select_statement = sales_table.select().limit(10)
    result_set = connection.execute(select_statement)
    for r in result_set:
        print(r)

    # Update
    update_statement = sales_table.update().where(sales_table.c.order_num==1105910).values(quantity=2, order_total=39)
    connection.execute(update_statement)

    # Confirm Update: Read
    reselect_statement = sales_table.select().where(sales_table.c.order_num==1105910)
    updated_set = connection.execute(reselect_statement)
    for u in updated_set:
        print(u)

    # Delete
    delete_statement = sales_table.delete().where(sales_table.c.order_num==1105910)
    connection.execute(delete_statement)

    # Confirm Delete: Read
    not_found_set = connection.execute(reselect_statement)
    print(not_found_set.rowcount)

error message:

(postgres-prac) E:\xfile\postgresql\postgres-prac>python postgres-sqlalchemy-core.py
Traceback (most recent call last):
  File "postgres-sqlalchemy-core.py", line 20, in <module>
    connection.execute(insert_statement)
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\engine\ba
se.py", line 1414, in execute
    return meth(
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\eleme
nts.py", line 485, in _execute_on_connection
    return connection._execute_clauseelement(
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\engine\ba
se.py", line 1630, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\eleme
nts.py", line 651, in _compile_w_cache
    compiled_sql = self._compiler(
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\eleme
nts.py", line 290, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compi
ler.py", line 1269, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compi
ler.py", line 710, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compi
ler.py", line 755, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\visit
ors.py", line 143, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\compi
ler.py", line 5317, in visit_insert
    crud_params_struct = crud._get_crud_params(
  File "E:\xfile\postgresql\postgres-prac\lib\site-packages\sqlalchemy\sql\crud.
py", line 326, in _get_crud_params
    raise exc.CompileError(
sqlalchemy.exc.CompileError: Unconsumed column names: order_type, quantity, cust
_name, discount, prod_number, price, order_total, order_num, prod_name

You define your table as an empty table:

sales_table = Table('sales', 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:

from sqlalchemy import Table, Column, Integer, String

user = Table(
    "user",
    metadata_obj,
    Column("user_id", Integer, primary_key=True),
    Column("user_name", String(16), nullable=False),
    Column("email_address", String(60)),
    Column("nickname", String(50), nullable=False),
)