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

[M.-A. Lemburg]

I think you are suggesting a new Python DB-API module for
accessing and querying Python namespaces.

This could probably be done by leveraging techniques from
Gadfly: http://gadfly.sourceforge.net/

Alternatively, you could mirror the namespaces into sqlite and
then use SQLite's SQL engine for the queries.


On 03.06.2013 20:05, big stone wrote:
> 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 at python.org
> http://mail.python.org/mailman/listinfo/db-sig
> 

-- 
Marc-Andre Lemburg
Director
Python Software Foundation
http://www.python.org/psf/