Creating database structures in a portable way

[Samuel]

Hi,

I am looking for a library that takes an XML file that specifies a
table structure, and generates the CREATE/DROP/ALTER SQL statements to
create the tables in the database.

In particular, I am trying to port a PHP application that currently
uses the AdoDB XML schema:

http://phplens.com/lens/adodb/docs-datadict.htm#xmlschema

Is there a way to do something similar with the DB-API modules in
Python?

Thanks,
-Samuel

 

[Bruno Desthuilliers]

Hi,

I am looking for a library that takes an XML file that specifies a
table structure, and generates the CREATE/DROP/ALTER SQL statements to
create the tables in the database.

In particular, I am trying to port a PHP application that currently
uses the AdoDB XML schema:


Is there a way to do something similar with the DB-API modules in
Python?

FWIW, there's a Python port of adodb:
http://phplens.com/lens/adodb/adodb-py-docs.htm

and parsing XML in Python is quite easy. So you could as well port the
AdoDB XML to Python too.

OTOH, there are other - possibly better (YMMV) - DB abstraction layers
in Python, like SQLAlchemy. And since the above solution requires
(re)writing the xml-parsing part, it might be worth rewriting it so it
knows how to generate SQLAlchemy schemas instead.

My 2 cents...

 

[Samuel]

FWIW, there's a Python port of adodb:

http://phplens.com/lens/adodb/adodb-py-docs.htm

and parsing XML in Python is quite easy. So you could as well port the
AdoDB XML to Python too.

That is exactly what I am trying to avoid. While implementing the
parser might be easy, you have to translate things into different
database flavors, caring about a bunch of things that I'd rather not
have to think about.

OTOH, there are other - possibly better (YMMV) - DB abstraction layers
in Python, like SQLAlchemy.

SQLAlchemy looks pretty good, but unfortunately apparently requires
shell access for installation (or at least, I have not found any other
solution covered in the docs), which I can not use. I need a solution
that can be shipped in a software package, and installed by simply
copying it to the server.

And since the above solution requires
(re)writing the xml-parsing part, it might be worth rewriting it so it
knows how to generate SQLAlchemy schemas instead.

Rewriting the schema is possible (I only want to keep it separated from
the code), so using SQLAlchemy's built in solution for generating
tables seems just fine. It's only the installation part.

Anyway, I am wondering; Python seems to include database adapters for
almost any important database, all with a unified API. Why would you
need another database abstraction on top of that?

Thanks,
-Samuel

 

[Bruno Desthuilliers]

Samuel a écrit :

That is exactly what I am trying to avoid. While implementing the
parser might be easy, you have to translate things into different
database flavors, caring about a bunch of things that I'd rather not
have to think about.

This I can understand pretty well - hence my suggestion of using
SQLAlchemy instead.

SQLAlchemy looks pretty good, but unfortunately apparently requires
shell access for installation (or at least, I have not found any other
solution covered in the docs), which I can not use. I need a solution
that can be shipped in a software package, and installed by simply
copying it to the server.

It doesn't use binaries AFAIK, so just copying should work as well.

Rewriting the schema is possible (I only want to keep it separated from
the code),

Why ? Isn't your code supposed to use it ?

so using SQLAlchemy's built in solution for generating
tables seems just fine. It's only the installation part.

cf above.

Anyway, I am wondering; Python seems to include database adapters for
almost any important database, all with a unified API. Why would you
need another database abstraction on top of that?

A first point is that the DB-API doesn't hide the differences between
various SQL dialects. A second point is that DB-API requires you to
embed SQL statements as strings, while SQLAlchemy allow you to build
your SQL queries in pure Python. (and FWIW, if using an existing DB, you
don't even have to describe the schema a second time to use it). Well,
just start playing with it, and I really doubt you'll want to come back
to the embedded hand-written SQL !-)

My 2 cents

 

[Samuel]

SQLAlchemy looks pretty good, but unfortunately apparently requires

It doesn't use binaries AFAIK, so just copying should work as well.

Indeed, from browsing the package it seems like a pure Python solution.
I installed it (but didn't test it yet because I have no code to drive
it yet).

Why ? Isn't your code supposed to use it ?

It often makes sense to use meta languages to enforce clean separation
of logic. In my experience it is helpful to separate the database logic
from the application logic.

A first point is that the DB-API doesn't hide the differences between
various SQL dialects.

I see.

A second point is that DB-API requires you to
embed SQL statements as strings, while SQLAlchemy allow you to build
your SQL queries in pure Python.

I'd rather not rewrite every single Sql statement into Python, since it
would

1. replace code that has already proven to work by something untested.
2. add yet another abstraction, at the cost of performance.

Luckily, SQLAlchemy also lets me use SQL statements directly.

(and FWIW, if using an existing DB, you
don't even have to describe the schema a second time to use it). Well,
just start playing with it, and I really doubt you'll want to come back
to the embedded hand-written SQL !-)

If I ever write something from scratch I'll use it.

Thanks for your comments, this was very helpful.

-Samuel