Hello,
I discovered recently the power of simplifying the integration between
python and its provided database sqlite3.
In my dream, I would like to be able to querry in "sql syntax" all objects
of python environnement.
So, I would suggest the next evolution of the Database API to
encourage/normalize the following evolution :
- authorize also the use of parameters in place of table names,
- the provided 'parameter for a table' should be :
. a python list,
. or a dictionary,
. or query result,
. or something similar like a pandas object.
- this parameter could be used anywhere in sql, but not authorised as an
"update".
Any "table like" object passed as a parameter will have :
- by default column names "c0", "c1", ... "cN" for each of it's N columns,
- unless it has an attribute ".description" which contains a list of string
names to be used as column title, it will be used instead of the default
values
Example :
************
#my_list is a python table
my_list=[('a', 1), ('b',2)]
# note the usage of ':mylist'
cur=con.execute("select :mylist.* from :my_list",{'my_list':my_list})
rows = cur.fetchall()
columns = [col_desc[0] for col_desc in cur.description]
print(columns)
print(rows)
(gives:)
c0 c1
a 1
b 2
# usage for a more complex work
# copy that table in a real sql table
cur=con.execute("create table keep_me as select :mylist.* from
:my_list",{'my_list':my_list})
Example of forbidden usage :
*******************************
# this is forbidden, fails :
cur=con.execute("update :mylist.c0 set c1=c1+1 ",{'my_list':my_list})
Implementation :
********************
- the case where the list given contains a constant number of columns and a
constant type in each columns is the 'normal' one,
- If the number of columns varies, only the number of columns in the first
columns is considered (or an error can be raised),
- default types are unknown (as it works best with sqlite3),
- if any type is provided in the object (like it may be found in a query
result), it is taken.
- if a non-simple object is passed, it is transform by "cpickle", or as a
blob, or an exception is raised, as prefered for a simple implementation.
- typically for sqlite :
. the :my_list table is created as a 'create temporary table
_tmp_my_list(c0,c1)
. before the creation a "drop table if exists _tmp_my_list" is generated,
. this temporary table is destroyed at the end of the execute.
Regards,
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
