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

[M.-A. Lemburg]

On 05.06.2013 20:55, big stone wrote:
> Hello M.-A.,
> 
> Afaik :
> "Linq" is not pure/transparent SQL

Linq is SQL embedded into a programming language. C# and VB are
the only ones supporting this at the moment, AFAIK.

> "Gadfly" is not SQL power (of true SQL motors).

Gadfly comes with its own SQL engine. It works in memory,
so provides excellent performance, and it's written in Python
(with a few C extensions for speed), so could be used as basis
for what you have in mind.

> My suggestion is :
> - use a true SQL  to query transparently a mix of python objects (lists,
> tuples...) AND already SQL tables,
> - simplify the upload of datas to SQL, as the upload is the painfull part
> of the sql standard :
> 
> Today :
>   query 1 million rows in sql= "select * from a_sql_table"
>   upload1 million rowsto sql  = ...(more complex than 3 words in a
> string)...
>   query 1 million rows of datas half in a sql table and half in a python
> table = ... (even more complex)..
> 
> With  the suggestion =
>   "select * from a_sql_table as s inner join :a_python_object as p on
> s.field0= p.c0"
> 
> 
> And all that being performant because :
> - the proposal would still use the SQL motors ,
> - to reduce the upload start :
>   . just begin your sql sequences by "create table x from select * from
> :a_python_object",
>   . use a SQL in memory database, what is just by luck SQlite with the
> ":"memry:" option.

I'm not sure whether people would really want to use SQL to
manipulate Python objects, but who knows... perhaps you'll
find a user community who'd love to work that way.

I think the closes we have to what you're suggesting is
pandas, which sort of provides ORM style SQL operations
on tables:

http://pandas.pydata.org/

I guess you could also write a SQL parser which transforms
the SQL or some Linq like dialect for Python into the method
calls for pandas.

> Regards,
> 
> 
> 2013/6/4 M.-A. Lemburg <mal at python.org>
> 
>> 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/
>>
> 

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