How can I insert a record with Pyodbc without having to specify all column names?

Question

Problem statement

I am using Pyodbc to do CRUD operations on a database. I am already aware of the drawbacks of using pyodbc, and so this is not for production code. This is only for internal testing of an API on a local copy of the database, so Pyodbc will do fine.

I would like to use a Pyodbc INSERT statement to create one new record in the database, all 260 fields of this record must be populated. I have reviewed the documentation of Pyodbc and its cursor objects, available here, on how to perform insert statements. Examples are provided below:

cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()

cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
cnxn.commit()

My question

In the above examples, attempts are made to parameterise the string, but in both cases the column names are still manually written into the query string. As are N number of '?', N being the number of columns. In my case however, I have 260 columns, which is very long. So my question is, can I create an insert string such that I don't have to specify ALL 260 column names? nor 260 '?'? I will be able to have all my data prepared in a list or a tuple, so the data is not the issue.

Why my question is different to other StackOverflow questions

My question is different to this one here pyodbc insert into sql. This question was asking about how one does an insert statement. And the answer merely pointed to documentation I already mentioned above. Meanwhile, the answer to how to insert variable into database with pyodbc? suggested to the user to parameterise the string with a ?. Which is fine, when your database table only has a few fields, however mine has 260. This question is closest conceptually to what I am asking, however it is in a different language. Many other questions appear to be asking how to insert many records into the database table, where, there are only a few columns. I only need to insert one record, but this one record has many fields.

What I have tried

I first tried to just do an INSERT statement by referencing the table name itself, and then the list of all 260 '?', followed by my data.

cursor.execute("INSERT INTO DB_NAME.dbo.TABLE_NAME VALUES ('?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?','?', '?', '?', '?', '?', '?','?', '?', '?','?', '?', '?','?'), my_prepared_data)

However I received the following error:

pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 260 parameters were supplied', 'HY000')

Surely, there must be a way to do this without manually specifying a string of (?,?,?,?,...,?,?,?), nor manually writing out all 260 column names?

Answer 1

Surely, there must be a way to do this without manually specifying a string of (?,?,?,?,...,?,?,?), nor manually writing out all 260 column names?

Yes, there is. You can use a tool like SQLAlchemy that will

1. perform inspection of the table to retrieve the column names for you, and
2. construct the SQL statement

import sqlalchemy as sa

engine = sa.create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")

my_table = sa.Table(
    "my_table", sa.MetaData(), schema="my_db.dbo", autoload_with=engine
)
col_names = list(col.name for col in my_table.columns)
print(col_names)
# ['col_1', 'col_2']

my_prepared_data = (1, "foo")
params = dict(zip(col_names, my_prepared_data))
print(params)
# {'col_1': 1, 'col_2': 'foo'}

engine.echo = True
with engine.begin() as conn:
    conn.execute(sa.insert(my_table), params)
"""DML emitted:
INSERT INTO my_db.dbo.my_table (col_1, col_2) VALUES (?, ?)
[generated in 0.00097s] (1, 'foo')
"""

Answer 2

Here's an answer for you on how you can insert a record with Pyodbc without having to specify all 260 column names.

What you'll first have to do is manually create a list of all 260 column names and store it.

For example:

column_names = ['column_name1', column_name2', ...,'column_name259', 'column_name260']

Then, you create a parameterised string, as you mentioned above, and insert these into the DB via that way. An example is below:

cursor.execute("insert into products(?) values (?)", column_names, data)
cnxn.commit()

Where data is the data you wish to insert.