[issue44927] [sqlite3] insert

[Erlend E. Aasland]

Erlend E. Aasland  added the comment:

The INSERT statement comes in many varieties. What about INSERT OR 
[ABORT,FAIL,IGNORE,REPLACE,ROLLBACK], INSERT WITH RECURSIVE, INSERT INTO table 
SELECT, etc.? Why only add such an API for insert; why not replace() as well?

I'm not convinced this is a good idea. It sounds to me like a project specific 
need that would need a project specific implementation. And as you showed, the 
implementation is pretty straight-forward.

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue44927] [sqlite3] insert

[Erlend E. Aasland]

Change by Erlend E. Aasland :


--
nosy: +erlendaasland

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue44927] [sqlite3] insert

[AR]

New submission from AR :

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