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 <m...@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@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/ _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
