Deviation from object-relational mapping (pySQLFace)

[sulyokpeti]

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.

 

[Paul Boddie]

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

 

[Bruno Desthuilliers]

(e-mail address removed) 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.

 

[Bruno Desthuilliers]

(e-mail address removed) 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) ?

 

[sulyokpeti]

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.

 

[sulyokpeti]

(e-mail address removed) 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.

 

[Bruno Desthuilliers]

(e-mail address removed) 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 !-)

 

[Paul Boddie]

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

 

[sulyokpeti]

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.


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

 

[sulyokpeti]

(e-mail address removed) a écrit :
(snip)


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.


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(Address.email_address=='(e-mail address removed)').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.

 

[J Peyret]

I would like to get some opinions on this approach.
Thanks.

I realize I will be minority here, but...

I've never quite understood why folks want to repeat the database's
metadata in XML files. I've gotten much better results just using
plain ol' SQL throughout, sprinkled in with generated-on-the-fly SQL.


1. A select clause identifies what is coming back from the db in the
cursor's description. 20 lines of code shoves that in a dictionary
for each row for any result set. 'Select * from <table>' works 90% of
the time for 1 table queries. What does XML add?

2. Inserts and deletes are relatively trivial to derive from
INFORMATION SCHEMA lookups on any given table and templates can be
generated for them. Updates are admittedly less trivial, but not
horribly so.

3. Query parameters can be added by simple %(<colname>)s embedded in
the query templates. That works great with dictionaries. You can
extract them with a regular expression and replace them with '?' and a
list, if your DB-API flavor requires that.

4. Plain ol' SQL can be cut and pasted in a query editor and can be
tested there.

5. If you unit test somewhat aggressively, any db-schema changes will
result in unhappy queries dying because they don't see the columns
that they expect in the resultsets. That keeps your Python code in
synch without feeding a layer of XML cruft.

6. XML is plain nasty for "simple local usage" where you don't need
to communicate with a 3rd party app or module. Conversely, XML is
great when you need to communicate data "somewhere else, potentially
with recursive and nested structures".

7. ANSI SQL is actually quite portable, if you know what to avoid
doing.

8. Last, but not least. Performance.

In complex processing on a database with large volumes, the last thing
you want to do is to fetch data to your client codeline, process it
there, and spew it back to the database. Instead you want to shoot
off series of updates/deletes/insert-selects queries to the server and
you want to rely on set-based processing rather than row-by-row
approaches. How do ORMs+XML help here?

My biggest hassle has been managing connection strings and catching
the weird Exception structures every Python db module figures it has
to re-implement, not the SQL itself.

Granted, if this were Java, you would need special data transfer
objects to encapsulate the results. But is not Java. And, also
granted, I _enjoy_ coding in SQL rather than trying to hide from it,
so YMMV.

Bottom line: SQL is extremely dynamic in nature, even more so than
Python. Why shackle it to static XML files?

P.S.

SQL Alchemy _is_ something I've been meaning to look at, because it
seems like they also _like_ SQL.

 

[Bruno Desthuilliers]

J Peyret a écrit :

I realize I will be minority here, but...

Then count me in - as long as all SQL stuff is cleanly encapsulated in
it's own module and called via appropriate functions / objects (have
mercy, no SQL in controler and views).

(snip - mostly agree)

 

[sulyokpeti]

I realize I will be minority here, but...

I've never quite understood why folks want to repeat the database's
metadata in XML files.  I've gotten much better results just using
plain ol' SQL throughout, sprinkled in with generated-on-the-fly SQL.

I guess you have not seen the examples: https://fedorahosted.org/pySQLFace/browser/examples
They help to understand what I wrote.

1.  A select clause identifies what is coming back from the db in the
cursor's description.  20 lines of code shoves that in a dictionary
for each row for any result set.  'Select * from <table>' works 90% of
the time for 1 table queries.  What does XML add?

Yes, you get the columns back without documentation for a programmer,
who does not now a thing about RBDMS.
If you are good at both python and that specific RDBMS you use, you
can make it.
XML is independent from both the programming language API-s and the
RDBMS specific 'retrieve the metadata' solutions.
This is a key to freely combine any programming language with any
RDBMS. If you put RDBMS specific features in your python code, your
solution will depend on that feature. If you don't, you loose the
feature.

2.  Inserts and deletes are relatively trivial to derive from
INFORMATION SCHEMA lookups on any given table and templates can be
generated for them.  Updates are admittedly less trivial, but not
horribly so.

True. But again the programmer has to know how to use a database,
which is not always the case.

3.  Query parameters can be added by simple %(<colname>)s embedded in
the query templates.    That works great with dictionaries.  You can
extract them with a regular expression and replace them with '?' and a
list, if your DB-API flavor requires that.

First time, I made the examples this way, but the code was ugly. So I
switched to lists (positional parameters).

4.  Plain ol' SQL can be cut and pasted in a query editor and can be
tested there.

Yes. The DB designer does it, and exports the SQL,DML with
documentation embedded in XML for the programming developers.
Excellent idea!

5.  If you unit test somewhat aggressively, any db-schema changes will
result in unhappy queries dying because they don't see the columns
that they expect in the resultsets.  That keeps your Python code in
synch without feeding a layer of XML cruft.

Who knows better any DB schema changes than a DB designer who exports
that XML?
If the change has an impact on the SQL interface, the programmers have
to be alerted of course.

6.  XML is plain nasty for "simple local usage" where you don't need
to communicate with a 3rd party app or module.  Conversely, XML is
great when you need to communicate data "somewhere else, potentially
with recursive and nested structures".

I guess you do not have the proof of this theorem.

7.  ANSI SQL is actually quite portable, if you know what to avoid
doing.

....and by using that, you loose the RDBMS specific features.

8.  Last, but not least.  Performance.

This is a bluff.

In complex processing on a database with large volumes, the last thing
you want to do is to fetch data to your client codeline, process it
there, and spew it back to the database.  Instead you want to shoot
off series of updates/deletes/insert-selects queries to the server and
you want to rely on set-based processing rather than row-by-row
approaches.  How do ORMs+XML help here?

I think there is a reason for server side programming too.

My biggest hassle has been managing connection strings and catching
the weird Exception structures every Python db module figures it has
to re-implement, not the SQL itself.

Connection string is also stored in my XML.

Granted, if this were Java, you would need special data transfer
objects to encapsulate the results.  But is not Java.  And, also
granted, I _enjoy_ coding in SQL rather than trying to hide from it,
so YMMV.

DB experts should not hide from SQL, but it is better to keep other
people away.

Bottom line:  SQL is extremely dynamic in nature, even more so than
Python.  Why shackle it to static XML files?

To develop both sides (DB design and client coding) independently.

P.S.

SQL Alchemy _is_ something I've been meaning to look at, because it
seems like they also _like_ SQL.

They do not _like_ SQL. They _like_ python.

 

[huy]

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.

Best use of XML for SQL generation/use I have seen is Ibatis SQLMAPS.

This focuses on the right things i.e queries and mapping values to/
from objects.

It would be great if python had such a tool.

Huy

 

[sulyokpeti]

Best use of XML for SQL generation/use I have seen is Ibatis SQLMAPS.

This focuses on the right things i.e queries and mapping values to/
from objects.

It would be great if python had such a tool.

Huy

I have looked into Ibatis SQLMAPS. It claims that its biggest
advantage is simplicity over other frameworks and ORMs. If you look
into my examples, you will see what simplicity is.

 

[Bruno Desthuilliers]

(e-mail address removed) a écrit :

I have looked into Ibatis SQLMAPS. It claims that its biggest
advantage is simplicity over other frameworks and ORMs.

Well... "Simplicity" is here to be taken relatively to the Java world
standards, I guess !-)