«

I have made a simple python module to handle SQL databases:
https://fedorahosted.org/pySQLFace/wiki
Its goal to separate relational database stuff (SQL) from algorythmic
code (python). A SQLFace is a facade initialized with a configuration
file (XML). It provides callable command objects for each sql query.
The call substitutes template variables with its parameters, and
returns the result of the query.
I would like to get some opinions on this approach.
Thanks.

On 12 Okt, 17:19, sulyokp...@gmail.com wrote:
> I have made a simple python module to handle SQL databases:
> https://fedorahosted.org/pySQLFace/wiki
> Its goal to separate relational database stuff (SQL) from algorythmic
> code (python). A SQLFace is a facade initialized with a configuration
> file (XML). It provides callable command objects for each sql query.
> The call substitutes template variables with its parameters, and
> returns the result of the query.
> I would like to get some opinions on this approach.

Not being a fan of object-relational mappers myself, I think that it's
worthwhile to explore other avenues that make database access more
convenient than plain DB-API usage, yet to still expose the benefits
of the database technology. I think that focusing on queries and
operations is the right thing to do, rather than to place the database
schema in a central position like most object-relational mappers do,
and I think that you've made the right decision in preserving the
queries instead of trying to erase all traces of SQL, but I'm not too
convinced by the usage of XML: what I've done myself in various
applications is to define query classes which declare the outputs from
each query as a list stored in a class attribute - something like
this:

class WeatherQuery(Query):

outputs = ["city", "temp_lo", "temp_hi", "prcp", "date"]
query = "SELECT city,temp_lo,temp_hi,prcp,date FROM weather"

Naturally, the superclass provides support for the actual query
execution, production of different output representations (such as
XML), and so on. If I wanted to make this more automatic (to stop
people squealing about "DRY" and the repetition of the column names,
although the outputs need not have the same names as the columns), I'd
probably want to parse the SQL (within reason, of course, since SQL is
quite a big language once you start to consider all the different
features).

Still, I don't think there's much to choose between what you've done
and what I've described above, and I think that there's definitely
merit in your approach.

Paul

a écrit :
> I have made a simple python module to handle SQL databases:
> https://fedorahosted.org/pySQLFace/wiki
> Its goal to separate relational database stuff (SQL) from algorythmic

s/algorythmic/algorithmic

!-)

> code (python). A SQLFace is a facade initialized with a configuration
> file (XML). It provides callable command objects for each sql query.
> The call substitutes template variables with its parameters, and
> returns the result of the query.
> I would like to get some opinions on this approach.

Going back to the wiki... Tell you later.

a écrit :
> I have made a simple python module to handle SQL databases:
> https://fedorahosted.org/pySQLFace/wiki
> Its goal to separate relational database stuff (SQL) from algorythmic
> code (python). A SQLFace is a facade initialized with a configuration
> file (XML). It provides callable command objects for each sql query.
> The call substitutes template variables with its parameters, and
> returns the result of the query.
> I would like to get some opinions on this approach.


First, I really don't see the point of XML for something as simple as
specifying a SQL query and a couple metadata. This would be better done
directly in Python using a metaclass, inheritance and a couple class
attributes, ie:

from SQLFace import Query, Statement

class WbsTotal(Query):
expression="SELECT hours,wbs FROM wbs_total"
out = ['hours', 'wbs']


class AddProject(Statement):
expression="""
INSERT INTO projects (project, description)
VALUES (%s, %s)
"""
in_ = ['project', 'description']


Also, I'd rather have Queries being iterators (delegating to the cursor)
instead of calling cursor.fetchall and returning the whole result.

My 2 cents

NB : btw, did you have a look at SQLAlchemy's low-level
python-relational integration part (*not* the 'orm' part) ?

On okt. 12, 19:54, Paul Boddie <p...@boddie.org.uk> wrote:
> On 12 Okt, 17:19, sulyokp...@gmail.com wrote:
>
> > I have made a simple python module to handle SQL databases:
> >https://fedorahosted.org/pySQLFace/wiki
> > Its goal to separate relational database stuff (SQL) from algorythmic
> > code (python). A SQLFace is a facade initialized with a configuration
> > file (XML). It provides callable command objects for each sql query.
> > The call substitutes template variables with its parameters, and
> > returns the result of the query.
> > I would like to get some opinions on this approach.
>
> Not being a fan of object-relational mappers myself, I think that it's
> worthwhile to explore other avenues that make database access more
> convenient than plain DB-API usage, yet to still expose the benefits
> of the database technology. I think that focusing on queries and
> operations is the right thing to do, rather than to place the database
> schema in a central position like most object-relational mappers do,
> and I think that you've made the right decision in preserving the
> queries instead of trying to erase all traces of SQL, but I'm not too
> convinced by the usage of XML: what I've done myself in various
> applications is to define query classes which declare the outputs from
> each query as a list stored in a class attribute - something like
> this:
>
> class WeatherQuery(Query):
>
> * outputs = ["city", "temp_lo", "temp_hi", "prcp", "date"]
> * query = "SELECT city,temp_lo,temp_hi,prcp,date FROM weather"
>
> Naturally, the superclass provides support for the actual query
> execution, production of different output representations (such as
> XML), and so on. If I wanted to make this more automatic (to stop
> people squealing about "DRY" and the repetition of the column names,
> although the outputs need not have the same names as the columns), I'd
> probably want to parse the SQL (within reason, of course, since SQL is
> quite a big language once you start to consider all the different
> features).
>
> Still, I don't think there's much to choose between what you've done
> and what I've described above, and I think that there's definitely
> merit in your approach.
>
> Paul

It is not convincing to look at an XML file alone. Let me give you an
example. Glade is a GTK+ application for creating GTK+ GUI. It
generates an XML file, that can be loaded in every programming
language that has libglade binding. Similarly, there could be a
database design tool to create a database, and save SQL/DML
expressions into an XML config file. Then you create the RDB command
objects by loading the XML in your favourite language. I think
programming languages are intended for describing neither relational
databases nor GUIs.

On okt. 13, 10:33, Bruno Desthuilliers <bruno.
42.desthuilli...@websiteburo.invalid> wrote:
> sulyokp...@gmail.com a écrit :
>
> > I have made a simple python module to handle SQL databases:
> >https://fedorahosted.org/pySQLFace/wiki
> > Its goal to separate relational database stuff (SQL) from algorythmic
> > code (python). A SQLFace is a facade initialized with a configuration
> > file (XML). It provides callable command objects for each sql query.
> > The call substitutes template variables with its parameters, and
> > returns the result of the query.
> > I would like to get some opinions on this approach.
>
> First, I really don't see the point of XML for something as simple as
> specifying a SQL query and a couple metadata. This would be better done
> directly in Python using a metaclass, inheritance and a couple class
> attributes, ie:
>
> from SQLFace import Query, Statement
>
> class WbsTotal(Query):
> * * expression="SELECT hours,wbs FROM wbs_total"
> * * out = ['hours', 'wbs']
>
> class AddProject(Statement):
> * * *expression="""
> * * * * * INSERT INTO projects (project, description)
> * * * * * VALUES (%s, %s)
> * * * * * """
> * * *in_ = ['project', 'description']
>
> Also, I'd rather have Queries being iterators (delegating to the cursor)
> instead of calling cursor.fetchall and returning the whole result.
>
> My 2 cents
>
> NB : btw, did you have a look at SQLAlchemy's low-level
> python-relational integration part (*not* the 'orm' part) ?

Typo corrected.
I have just posted a message explaining the point of the separate XML
config file. Additionaly I do not like programming languages
intermixed with an other languages like SQL or HTML.
The result of the query is actually a list, so you have your iterator.
Although this fetchall solution is not suitable in case of a large
result set written to a stream. So I take this into consideration for
improving the query. Thanks.

a écrit :
(snip)
> It is not convincing to look at an XML file alone. Let me give you an
> example. Glade is a GTK+ application for creating GTK+ GUI. It
> generates an XML file, that can be loaded in every programming
> language that has libglade binding.
> Similarly, there could be a
> database design tool to create a database, and save SQL/DML
> expressions into an XML config file.

Why so ? What's wrong with a plain SQL file ? We already have a language
for RDBMS schema description, and the schema description is itself
stored in the RDBMS catalog so the SQL description can be regenerated
from the RDBMS. I just don't see the point of storing all this in XML.

> Then you create the RDB command
> objects by loading the XML in your favourite language.
> I think programming languages are intended for describing neither relational
> databases nor GUIs.

SQLAlchemy is an interesting attempt at integrating the relational model
in a programming language.

Ok, I don't mean neither of us is necessarily right and the other wrong
- different POV, mostly, so I guess we can at least agree to disagree !-)

On 14 Okt, 00:43, sulyokp...@gmail.com wrote:
>
> It is not convincing to look at an XML file alone. Let me give you an
> example. Glade is a GTK+ application for creating GTK+ GUI. It
> generates an XML file, that can be loaded in every programming
> language that has libglade binding. Similarly, there could be a
> database design tool to create a database, and save SQL/DML
> expressions into an XML config file. Then you create the RDB command
> objects by loading the XML in your favourite language.

I'd agree that XML makes a good interchange representation which saves
everyone from having to parse various things, but having worked a bit
with relational databases and having had to actively manage their
schemas, I have to say that my primary representation for a schema is
SQL/DDL, and that my primary representation for queries is also SQL.
Now, there's a lot to be said for making that SQL more consumable, and
I've done a little work on converting SQL to XML (as have many others)
in order to make life easier for, say, tool authors, and I'd even go
as far as saying that it should be possible to convert XML back to
SQL, but in doing so there would potentially remain a need for the XML
dialect to be as expressive as SQL, which then means that you have to
replicate SQL in XML.

> I think programming languages are intended for describing neither relational
> databases nor GUIs.

The above discussion is somewhat tangential to what you've done,
though, and I certainly didn't mean to say that the use of XML was in
any way "wrong", especially in the way you've been using it. I suppose
that when you state the above about programming languages, you
actually mean languages other than SQL. Even so, I'd much rather use
SQL to describe a database table than one of the many different Python-
based, class-plus-attributes representations so beloved of the various
object-relational mappers.

I look forward to seeing where you take your project in future,
however.

Paul

On okt. 14, 13:26, Paul Boddie <p...@boddie.org.uk> wrote:
> On 14 Okt, 00:43, sulyokp...@gmail.com wrote:
>
>
>
> > It is not convincing to look at an XML file alone. Let me give you an
> > example. Glade is a GTK+ application for creating GTK+ GUI. It
> > generates an XML file, that can be loaded in every programming
> > language that has libglade binding. Similarly, there could be a
> > database design tool to create a database, and save SQL/DML
> > expressions into an XML config file. Then you create the RDB command
> > objects by loading the XML in your favourite language.
>
> I'd agree that XML makes a good interchange representation which saves
> everyone from having to parse various things, but having worked a bit
> with relational databases and having had to actively manage their
> schemas, I have to say that my primary representation for a schema is
> SQL/DDL, and that my primary representation for queries is also SQL.
> Now, there's a lot to be said for making that SQL more consumable, and
> I've done a little work on converting SQL to XML (as have many others)
> in order to make life easier for, say, tool authors, and I'd even go
> as far as saying that it should be possible to convert XML back to
> SQL, but in doing so there would potentially remain a need for the XML
> dialect to be as expressive as SQL, which then means that you have to
> replicate SQL in XML.
>
> > I think programming languages are intended for describing neither relational
> > databases nor GUIs.
>
> The above discussion is somewhat tangential to what you've done,
> though, and I certainly didn't mean to say that the use of XML was in
> any way "wrong", especially in the way you've been using it. I suppose
> that when you state the above about programming languages, you
> actually mean languages other than SQL. Even so, I'd much rather use
> SQL to describe a database table than one of the many different Python-
> based, class-plus-attributes representations so beloved of the various
> object-relational mappers.
>
> I look forward to seeing where you take your project in future,
> however.
>
> Paul

I agree more than you thought. I do not want to replace SQL with XML
either. I only use XML to interchange data (SQL/DML) between the
database designer (tool) and the application interface in order to
create a specific database interface for a certain database. It is the
focus of database design using different RDBMS-s and different
programming languages. There is an other reason for XML. At this point
my XML structure contains the SQL/DML expressions as you use it in sql
clients, and some metadata to generate documentation using XSL
transformation. That's why I provided that sqlface.xsl in the src
directory. Yes I have to relocate DTD and XSL from the python source
directory.
Relational stuff stays in the relational language SQL. Data
processing, and business logic goes into a programming language like
python. Otherwise I plan to support other languages with such an SQL
interface.

Peti

On okt. 14, 10:09, Bruno Desthuilliers <bruno.
42.desthuilli...@websiteburo.invalid> wrote:
> sulyokp...@gmail.com a écrit :
> (snip)
>
> > It is not convincing to look at an XML file alone. Let me give you an
> > example. Glade is a GTK+ application for creating GTK+ GUI. It
> > generates an XML file, that can be loaded in every programming
> > language that has libglade binding.
> > Similarly, there could be a
> > database design tool to create a database, and save SQL/DML
> > expressions into an XML config file.
>
> Why so ? What's wrong with a plain SQL file ? We already have a language
> for RDBMS schema description, and the schema description is itself
> stored in the RDBMS catalog so the SQL description can be regenerated
> from the RDBMS. I just don't see the point of storing all this in XML.
>
> > Then you create the RDB command
> > objects by loading the XML in your favourite language.
> > I think programming languages are intended for describing neither relational
> > databases nor GUIs.
>
> SQLAlchemy is an interesting attempt at integrating the relational model
> in a programming language.
>
> Ok, I don't mean neither of us is necessarily right and the other wrong
> - different POV, mostly, so I guess we can at least agree to disagree !-)

Plain SQL does not have a structure to easily handle metadata. XML has
several parsers, transformators like xmlto.
I am not going to reimplement relational stuff in XML or any
programming language. In my approach relational model is described in
SQL, processing is in a programming language, and XML is used for
interchange data. That data is actually SQL, and metadata to create
documentation.

I have looked into SQLAlchemy. I have seen this:
users_table = Table('users', metadata, Column('id', Integer,
primary_key=True), Column('name', String), ...
session.query(User,
Address).filter(User.id==Address.user_id).filter(A ddress.email_address=='').all()
users_table = Table('users', metadata, Column('id', Integer,
primary_key=True), Column('name', String), ...
....and I do not like it.
My favourite programming language is python because of its simple and
practical syntax. SQLAlchemy is something different, something like
what I do in full time, and something I am fed up with.