Unconsumed column names sqlalchemy python

user_9855588 0
Viewed 5129

I am facing the following error using SQLAlchemy: Unconsumed column names: company

I want to insert data for 1 specific column, and not all columns in the table: INSERT INTO customers (company) VALUES ('sample name');

My code:

engine.execute(table('customers').insert().values({'company': 'sample name'}))

Create Table:

'CREATE TABLE `customers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `company_UNIQUE` (`company`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8'
sqlalchemy python
2 Answers

After hours of frustration, I was able to test a way that I think works for my use case. As we know, you can insert to specific columns, or all columns in a table. In my use case, I dynamically need to insert to the customers table, depending on what columns a user has permissions to insert to.

I found that I needed to define all columns in the table() method of sqlalchemy, but I can pass in whatever columns and values that I need dynamically to the values() method.

Final code:

engine.execute(table('customers', column('company'), column('first_name'), column('last_name'), column('email'), column('phone')).insert().values({'company': 'sample name'}))
user_98555880

The original solution works great, however I'd like to add another approach that will allow working with tables dynamically, without specifying all of their columns. This can be useful when working with multiple tables.

We can use the Table class from sqlalchemy.schema and provide our engine to its autoload_with parameter, which will reflect the schema and populate the columns for us.

Then, we can work just like we in the OP's answer.

from sqlalchemy.schema import Table, MetaData

my_table_name = 'customers'  # Could be passed as an argument as well :)
table = Table(my_table_name, MetaData(), autoload_with=engine)

engine.execute(my_table.insert({'company': 'sample name'}))
user_47169220
Related