[DB-SIG] Make Python/SQL integration even smoother

[big stone]

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,
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20130603/903b6019/attachment.html>