Database recommendations for Windows app

[Dave Cook]

Your list didn't mention a few things that might be critical.
Referential integrity?

You can implement it in sqlite with triggers. I only bother with cascading
delete triggers, myself.

Type checking? SQLite currently supports
neither.

sqlite3 has a "strict affinity" mode, but I'm not exactly sure how one sets
it.

http://www.sqlite.org/datatype3.html

Dave Cook

 

[Joel Rosdahl]

[...]

Can anyone recommend a database that runs on Windows, is fast /
efficient and can be shipped without restrictions or extra
downloads?

http://pysqlite.org

Or APSW <http://www.rogerbinns.com/apsw.html>.

 

[Magnus Lycka]

OK, I'm with you part of the way. Typical "Access" developers
are *always* involved with DLL hell, right? You're surely not
saying that Python worsens that frustration, are you?

I think Dan was commenting on flaws in Microsoft's products,
not in Python. As I understand it, he was suggesting to use
something else than Access with Python, not something else
than Python with Access. The O.P. wanted a database for his
Python app, and Thomas Bartkus suggested Access.

 

[Dave Cook]

Or APSW <http://www.rogerbinns.com/apsw.html>.

Interesting. I was hoping it would not have one pysqlite2 limitation: if
you have an empty database, cursor.description always returns None, even if
you have "pragma empty_result_callbacks=1" (pysqlite 1.x doesn't have the
problem). But apsw also requires data to be avaliable before you can get
column descriptions.

However, the tracing stuff and the various hooks you can set look really
interesting.

Dave Cook

 

[Jussi Jumppanen]

Firebird might be a contender...

I recently completed a 5 user Java based Windows reporting
system that used Firebird as the SQL server based database.

I found Firebird performed very well and I would not hesitate
to use it again.

Jussi Jumppanen
Author of: Zeus for Windows Editor (New version 3.94 out now)
"The C/C++, Cobol, Java, HTML, Python, PHP, Perl folding editor"
Home Page: http://www.zeusedit.com

 

[Thomas Bartkus]

I think Dan was commenting on flaws in Microsoft's products,
not in Python. As I understand it, he was suggesting to use
something else than Access with Python, not something else
than Python with Access.

> The O.P. wanted a database for his
> Python app, and Thomas Bartkus suggested Access.

Not exactly!

I suggested the built in Microsoft DAO or ADO database libraries which he
could use without need to distribute with his app. The Access application
is simply another client app that sits on top of DAO/ADO and would be quite
unnecessary here. Any Python/DB application you wished to distribute for MS
Windows would do best talk to the ADO library directly - end of distribution
problems.

* Everyone with WindowsXP already has the DAO and ADO libraries.

* Not everyone has (or needs) MS Access which one would have to pay for and
could not distribute freely with ones Python app.

* Python has no need of MS Access in order to create, maintain, and
manipulate databases using Microsofts built in ADO database facilities -
although a developer *might* find Access useful as an inspection/debugging
tool on his own workstation.

All of which used to confuse the hell out of me
Thomas Bartkus

 

[Cameron Laird]

.
.

sqlite3 has a "strict affinity" mode, but I'm not exactly sure how one sets
it.

http://www.sqlite.org/datatype3.html

.
.
.
While I'm still learning SQLite3, I already know enough to
reinforce Mr. Cook's point, and report that Version 3 re-
tains the data manager's yummy lightness, while
significantly enhancing its functionality in such regards
as type correctness.

 

[Magnus Lycka]

Not exactly!

Sorty, I meant Jet or whatever the backend is called these days.

I suggested the built in Microsoft DAO or ADO database libraries which he
could use without need to distribute with his app. The Access application
is simply another client app that sits on top of DAO/ADO and would be quite
unnecessary here. Any Python/DB application you wished to distribute for MS
Windows would do best talk to the ADO library directly - end of distribution
problems.

If we start with vanilla Python, we need just the tiny PySqlite module
and DB-API compliant Python code to get a SQLite solution to work. One
small 3rd party module which is trivial to bundle. There is no way you
can access ADO with less 3rd party stuff bundled than that. The minimum
is to bundle win32all or ctypes, but then you need to work much harder.
You probably want a 3rd party python ADO library as well. Then it's
much more stuff to bundle.

 

[Thomas Bartkus]

I think Dan was commenting on flaws in Microsoft's products,
not in Python. As I understand it, he was suggesting to use
something else than Access with Python, not something else
than Python with Access. The O.P. wanted a database for his
Python app, and Thomas Bartkus suggested Access.

Not exactly!

I suggested the built in Microsoft DAO or ADO database libraries which he
could use without need to distribute with his app. The Access application
is simply another client app that sits on top of DAO/ADO and would be quite
unnecessary here. Any Python/DB application you wished to distribute for MS
Windows would do best talk to the ADO library directly - end of distribution
problems.

* Everyone with WindowsXP already has the DAO and ADO libraries.

* Not everyone has (or needs) MS Access which one would have to pay for and
could not distribute freely with ones Python app.

* Python has no need of MS Access in order to create, maintain, and
manipulate databases using Microsofts built in ADO database facilities -
although a developer *might* find Access useful as an inspection/debugging
tool on his own workstation.

All of which used to confuse the hell out of me
Thomas Bartkus

 

[Thomas Bartkus]

Sorty, I meant Jet or whatever the backend is called these days.

Hey! Even MS is confused these days.

If we start with vanilla Python, we need just the tiny PySqlite module
and DB-API compliant Python code to get a SQLite solution to work. One
small 3rd party module which is trivial to bundle. There is no way you
can access ADO with less 3rd party stuff bundled than that. The minimum
is to bundle win32all or ctypes, but then you need to work much harder.
You probably want a 3rd party python ADO library as well. Then it's
much more stuff to bundle.

I was thinking of Win32com which I expect lets you put a wrapper around ADO
and work the ADO (or any other ActiveX) object model from within Python.

However

I must confess that while I am quite familiar with ADO, I haven't used it
with Python.

I do know that the ADO (or DAO) libraries are complete, SQL oriented,
database systems that are available on every WinXP desktop. I *think* "Jet"
refers to the underlying, .mdb file based storage engine that ADO rides on
top of by default. All WinXP platforms have this and do not need another db
platform - IOW we don't need to distribute a db platform here!

Unless one simply prefers something else ;-)
Thomas Bartkus

 

[Dan]

?

OK, I'm with you part of the way. Typical "Access" developers
are *always* involved with DLL hell, right? You're surely not
saying that Python worsens that frustration, are you?

No.

 

[Dan]

Your list didn't mention a few things that might be critical.
Referential integrity? Type checking? SQLite currently supports
neither. Just make sure you check the list of supported features to see
that it really does what you need.

-Peter

So in SQLLite, what happens of you try to store XYZ in an integer field?

 

[Peter Hansen]

So in SQLLite, what happens of you try to store XYZ in an integer field?

Without your having enabled any of the "affinity" options mentioned by
Dave Cook, SQLite will happily store 'XYZ' in that column, and return it
to you when you query that row. The types are either ignored, or
advisory, or meaningful in various ways, depending on the settings you pick.

Note that this is considered something as a desirable feature in the
SQLite community, in a similar fashion (it appears to me) to how dynamic
typing is considered in the Python community, so don't consider it
outright to be a Bad Thing. I'd say more on the issue, if I knew
anything that I hadn't just read in the documentation. ;-)

-Peter

 

[Magnus Lycka]

I was thinking of Win32com which I expect lets you put a wrapper around ADO
and work the ADO (or any other ActiveX) object model from within Python.

Sure, but since others have made wrappers around ADO for Python before,
you'd either reivent the wheel or or use e.g.
http://www.ecp.cc/pyado.html or http://adodbapi.sourceforge.net/
and get another dependency besides the Win 32 libs.

Your milage may vary, but I prefer to use the DB-API compliant
interfaces. If mxODBC is ok from a licence point of view, I'm sure
it's an excellent product, but if you use it for .mdb I suspect you
need to deal with Jet oddities like quoting dates with # and
non-standard wildcard symbols. (* and ? instead of % and _). That
was the case last time I tried.

Maybe modern Jet versions have done away with those absurdities, but
then I guess you are in trouble if you install the program on a machine
with somwhat older Windows software.

If the limited SQL support in SQLite is enough, I think it's a very
simple and straight forward tool to use in Windows from Python.
Try it!