New submission from AR <[email protected]>:
I propose to add an insert method to a SQLite cursor object, believe this could
greatly improve readability of python code when you have to do a lot of insert
operations to a different tables.
Currently we have to create a dictionary with SQL's for each table:
insert_sql = {'form_a': """INSERT INTO table_a (field_a1, field_a2) VALUES(:f1,
:f2)""",
'form_b': """INSERT INTO table_b (field_b1, field_b2, field_b3)
VALUES(:f1, :f2, :f3),
....other SQL statements }"""
or we may use version with unnamed parameters:
insert_sql = {'form_a': """INSERT INTO table_a (field_a1, field_a2) VALUES(?,
?)""",
'form_b': """INSERT INTO table_b (field_b1, field_b2, field_b3)
VALUES(?, ?, ?),
....other SQL statements }"""
The first one is conveniently compatible with cx_Oracle bind variable syntax,
rows that are inserted are essentially dictionary. As dictionary is a mutable
type, some extra freedom during construction of the row is allowed.
The second syntax(qmark style) is specific to SQLite, rows that are inserted
should have tuple type (namedtuple for us to be able to extract field names at
later stage).
entries_dict = [{'field_a1': 100, 'field_a2': 'sample1'},
{'field_a1': 500, 'field_a2': 'sample2'}]
DataRow = namedtuple('DataRow', ['field_a1', 'field_a2'])
entries_namedtuple = [DataRow(101, 'sample3'), DataRow(505, 'sample4')]
In order to do an insert, you have to use either execute, or executemany:
cursor.executemany(insert_sql['form_a'], entries_dict)
or
cursor.execute(insert_sql['form_a'], entries_dict[0])
Now let's move towards future insert method of cursor. As a first step, lets
create SQL statement on the fly:
table_name = 'table_a'
#in case of a list of dictionaries:
sql = """INSERT INTO {} ({}) VALUES({})""".format(table_name, ',
'.join([str(key) for key in entries_dict[0]]),
', '.join([':' + str(key) for key in
entries_dict[0]]))
#currently, to do an insert operation, we have to:
cursor.executemany(sql, entries_dict)
#in case of a list of namedtuples:
sql = """INSERT INTO {} ({}) VALUES({})""".format(table_name, ',
'.join([str(field) for field in entries_namedtuple[0]._fields]),
', '.join(['?' for field in
entries_namedtuple[0]._fields]))
#currently, to do an insert operation, we have to:
cursor.executemany(sql, entries_namedtuple)
Now back to the proposal of insert method with unified syntax (one/many and
dict/namedtuple). Let's do a second step and add an Insert method to a Cursor.
The idea is to provide this method with table name, extract column names from
supplied dict/namedtuple and use SQL generators from above. Than we could
replace old cursor.executemany syntax with:
cursor.insert(table_name, entries_dict)
or
cursor.insert(table_name, entries_dict[0])
or
cursor.insert(table_name, entries_tuple)
Since we may insert all, or any row of two types, this looks even more pythonic
than pymongo(MongoDB) approach:
collection.insert_many(entries_dict)
Actually, the fact that pymongo insert code is so much cleaner and concise drew
my attention. Other aspects of that lib are totally different story.
I do not propose to generalize, or to move towards ORM or pymongo way of doing
things. The scope is limited - lets do a convenient insert.
Simplified implementation could be like this:
def insert(self, table_name, entries):
if(type(entries) == list): # several records(rows) need to
be inserted
do_insert = self.executemany
if(hasattr(entries[0], '_fields')): #NamedTuple
sql = "INSERT INTO {} ({}) VALUES({})".format(table_name, ',
'.join([str(field) for field in entries[0]._fields]),
', '.join(['?' for field in
entries[0]._fields]))
elif(type(entries[0] == dict)): #dict
sql = "INSERT INTO {} ({}) VALUES({})".format(table_name, ',
'.join([str(key) for key in entries[0]]),
', '.join([':' + str(key) for key in
entries[0]]))
else: #just one record(row)
do_insert = self.execute
if(hasattr(entries, '_fields')): #NamedTuple
sql = "INSERT INTO {} ({}) VALUES({})".format(table_name, ',
'.join([str(field) for field in entries._fields]),
', '.join(['?' for field in
entries._fields]))
elif(type(entries == dict)):
sql = "INSERT INTO {} ({}) VALUES({})".format(table_name, ',
'.join([str(key) for key in entries]),
', '.join([':' + str(key) for key in
entries]))
do_insert(sql, entries)
If proposal is not feasible/doesn’t fit to a broad concept, I suggest to
mention in documentation
- list comprehension one-line SQL-generators (see above)
- remind users who create list of dictionaries for bulk insert that a copy of
the dict should be used. Otherwise all dicts inside a list would be the same
entries_dict.append(entry_dict.copy()). Definitely, as namedtuple is immutable,
no need for extra steps for a list of namedtuples.
----------
components: Extension Modules
messages: 399663
nosy: AR
priority: normal
severity: normal
status: open
title: [sqlite3] insert
type: enhancement
versions: Python 3.9
_______________________________________
Python tracker <[email protected]>
<https://bugs.python.org/issue44927>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
