CODEWITHSUNDEEP
pythonsqlalchemy
dataviews
dataviews

Reputation: 3090

Unconsumed column names sqlalchemy python

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'

Upvotes: 10

Views: 20637

Answers (2)

mati.o
mati.o

Reputation: 1768

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'}))

Upvotes: 8

dataviews
dataviews

Reputation: 3090

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'}))

Upvotes: 10

Related Questions