Is it just me, or is Sqlite3 goofy?

[Dennis Lee Bieber]

Are you saying that MySQL is goofy? ;-)

No... Just very common... and hopefully not a bug-ridden as that
other piece of common so-called software: Windows

As I said before, I'm not entering 500,000 records by writing
INSERT statements for each record, so reading csv files is
a more realistic test. Nevertheless, I am still convinced that

It does come down, a bit, to what the input data is... As you saw in
my (way too many) posts is that both SQLite and MySQL accepted character
data that came in from a text (CSV) file, regardless of field type --
numeric-only data would be properly converted to the numeric field type;
SQLite would take non-numeric data as-is, MySQL would (I should have
tried with something like "6<6") initialize to 0 and take whatever the
conversion then "added" to that 0 (of the two behaviors, I think I
prefer SQLite's which does not result in data loss).

They also accepted data that had been converted to the nearest
Python equivalent datatype -- though doing such a conversion would
result in exceptions if the data could not be converted. If the input
data is coming from a text file (such as the CSV), then all fields are
character unless explicitly converted by the application. If the data
were coming from a binary file, using the struct module to extract
binary numerics, the problem of a mal-formed numeric is moot -- at the
worst, a sequence of bytes decodes to something like NaN in IEEE floats.

the documentation (or lack thereof) is mainly responsible for
my confusion. I was, after all, mimicing the examples given
(which still have errors).

I think an explanation of how Sqlite3 differs from SQL and
a better set of examples is still warranted.

Unfortunately, I don't think they are going to duplicate the 200 or
so page O'Reilly SQLite book as part of the help system (even if that
book is quite out-of-date; there is one skinny chapter near the end that
explains what changes "will appear" in the version that has been
available for Python for over a year now).
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Magnus Lycka]

I think an explanation of how Sqlite3 differs from SQL and
a better set of examples is still warranted.

In general, Python standard library modules that are wrappers
for third party libraries are very thinly documented, and they
should probably remain that way, because it's really too much of
a burden on the Python developers to develop this documentation
and keep it up to date. The idea is to document the wrapper, not
the wrapped library.

If I had a choice concerning these wrapper libraries, I'd much
rather see more docs on the tkinter and xml libraries. There you
need to guess a lot. There is no shortage of Tcl/Tk docs, but it
doesn't look the same within Python. For the Python xml libraries,
I've had to experiment a lot, and I have this nagging feeling
that I don't do things the way I should. (From Python 2.5, we
have ElementTree, which is much better from this perspective,
but we've just started using 2.4 at work, and we try to keep
the amount of third party libraries to a minimum here.)

It seems to me that the sqlite3 module is fairly decent in
this regard, particularly since it's not even included in a
completed Python release yet. Concerning the API, I'm surprised
to see magic method naming such as __conform__ introduced in
a library like that. It seems to me that this is a violation
of PEP 8. I'm sure there are further details that could be worth
mentioning in the docs, but I think it's important that we don't
try to duplicate the SQLite docs in the Python docs.

 

[Bruno Desthuilliers]

Ah, *you* haven't read the documentation either!

I use SQLite and PgSQL (and, alas, MySQL) on a daily basis. So I've *of
course* read the doc (and frequently re-read it).

"as SQL-compliant as possible"?

Yes. And yes, I know about the typing issue. If you want to make sure
you can migrate your data to a real RDBMS, then it's up to you to take
appropriate precautions (FormEncode can be a real helper here...).

This (documented enough) issue apart, we have far less troubles with
SQLite than we have with MySQL, on both the dev and admin sides.

And FWIW, insulting peoples won't buy you much.

(snip useless rant again)

 

[Bruno Desthuilliers]

Are you saying that MySQL is goofy? ;-)

This is an understatement.

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

Who has reviewed sqlite/pysqlite after all?

pysqlite was actually reviewed by several Python core developers before
becoming a part of the Python standard library, most prominently Neil
Norwitz and Anthony Baxter.

Reading the passage in the
sqlite FAQ I can hardly believe that passing errors silently and
coercing everything to string when it fails to be coerced to INTEGER
although INTEGER was an invariant declared in the create command is on
par with Pythons design philosophy. [...]

Unfortunately, third-party library authors don't first check with
Python's design philosophy in case their software will be wrapped as a
Python module ;-)

I did my best to combine both SQLite's and Python's type system though,
including a discussion with pysqlite 1.x users before the grand rewrite
for version 2, which is the one that ended up in the Python standard
library now.

In other cases doctoral dissertations are written about whether a
keyword or some punctuation shall be used for decorator syntax and in
this case everything must be rushed into the code base of the
standard library?

There was no rush at all.

-- Gerhard

 

[Preston Hagar]

Unfortunately, I don't think they are going to duplicate the 200 or

so page O'Reilly SQLite book as part of the help system (even if that
book is quite out-of-date; there is one skinny chapter near the end that
explains what changes "will appear" in the version that has been
available for Python for over a year now).
--

Just to let you (and everyone else know) there is a new SQLite book
out from APress that covers SQLite 3

http://www.apress.com/book/bookDisplay.html?bID=10130

It actually has a section that covers what a lot of these postings
have been discussing, check constraints. You can actually implement
type checking constraints in SQLite with very little additional code.
That way it will give you an error message if you try to insert
something of the wrong type.

HTH,

Preston

 

[Gabriel Genellina]

I would be surprised if they had never used ANY database. A little
thing like dynamic field typing will simply make it impossible to
migrate your Sqlite data to a *real* database.

Why not? Because it breaks the relational model rules? That model
certainly was great 30 years ago, but now things are different. (In
fact, you didn't menction the word "relational", but I presume you
were thinking of that).
Even what you call *real* databases have a lot of incompatibilities
among them (e.g. ORACLE does not provide an "autoincrement" type, but
has sequences, and so on...). Of course you could restrict yourself
to, by example, SQL92 entry level and be a lot more compatible.
But if I'm using a nice OO language like Python which lets me bind
*any* object to *any* name, why should be wrong to bind *any* object
to *any* database column? Looks a lot more "pythonic" for me. Of
course, a true object database (like ZODB) is better.



Gabriel Genellina
Softlab SRL





__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

 

[mensanator]

Why not? Because it breaks the relational model rules?

That's part of it.

That model
certainly was great 30 years ago, but now things are different.

Different only in "lite" databases.

(In
fact, you didn't menction the word "relational", but I presume you
were thinking of that).
Even what you call *real* databases have a lot of incompatibilities
among them (e.g. ORACLE does not provide an "autoincrement" type, but
has sequences, and so on...).

But it was stated in the sqlite docs that ALL SQL databases
use static types implying that sqlite will be incompatible
with any "heavy" database should the need arise to migrate
upwards. The issue is not that there will be compatibilty
problems with any data migration but that the truth is exactly
opposite of what's claimed in Section 13.13.

I'm not saying sqlite can't be used, what I'm asking for
is that the documentation lay the facts out and I'll decide
whether I can make this work in my application. Lying about
it makes you sound like Microsoft.

Of course you could restrict yourself
to, by example, SQL92 entry level and be a lot more compatible.
But if I'm using a nice OO language like Python which lets me bind
*any* object to *any* name, why should be wrong to bind *any* object
to *any* database column?

But SQL isn't OO, it's relational. That means JOINing tables
together on a common field. In theory, due to the comparison
hierarchy, it is impossible to do JOINs with dynamic typing
since different types can never be equal. In practice, the type
affinity kluge trys to work around this but can't do anything
if the string doesn't look like an integer when a text field
attempts to JOIN to an interger field.

Looks a lot more "pythonic" for me.

If all you have is a hammer, everything looks like a nail.

 

[MonkeeSage]

But it was stated in the sqlite docs that ALL SQL databases
use static types implying that sqlite will be incompatible
with any "heavy" database should the need arise to migrate
upwards. The issue is not that there will be compatibilty
problems with any data migration but that the truth is exactly
opposite of what's claimed in Section 13.13.

I'm not saying sqlite can't be used, what I'm asking for
is that the documentation lay the facts out and I'll decide
whether I can make this work in my application. Lying about
it makes you sound like Microsoft.

I thought your qualm was with the pysqlite docs, not the sqlite docs
(which apparently do make it plain how the database handles typing)?

Also, as others have mentioned, there are a number of ways to ensure
type safety, as long as you know how the database works (which as I
understand was your original point -- that it should be better
documented how it works in the pysqlite docs; and I am inclined to
agree -- at least a mention with link to the sqlite docs would be
helpful). But given that type safety is not an issue if you use those
ways of ensuring it, then the move to a fuller database _will_ be
relatively easy. If you don't want to change anything in your database
creation/update code ala check constraints, you can always explicitly
validate from python, which can be done programatically (very simple
example -- you could also use regexp patterns to validate; e.g., string
fields not only must be type str, but must not match '^\d+$', &c):

rows = [
['1', 'fred', '0051', '/home/fred'],
['2', 'bob', '0054', '/home/bob'],
['3', 'bork', '>056', '/home/bork']
]
def validate(row):
return [int(row[0]), str(row[1]), int(row[2]), str(row[3])]
for i in xrange(len(rows)):
rows = validate(rows) # <- throws an exception on the third row
# database stuff here...

Regards,
Jordan

 

[Mike Owens]

But it was stated in the sqlite docs that ALL SQL databases
use static types implying that sqlite will be incompatible
with any "heavy" database should the need arise to migrate
upwards. The issue is not that there will be compatibilty
problems with any data migration but that the truth is exactly
opposite of what's claimed in Section 13.13.

Implying? There's a solid word. Migrating data from SQLite to other
databases is no more difficult or easy than migrating data to any
other database. Do you think this is ever trivial? It's as hard or as
easy as you make it. No database can just take any schema and the data
you put in it and just magically convert that schema/data to
flawlessly work in any arbitrary database of your choosing. Some
databases have tools to help with this, but they still are not
perfect.

I'm not saying sqlite can't be used, what I'm asking for
is that the documentation lay the facts out and I'll decide
whether I can make this work in my application. Lying about
it makes you sound like Microsoft.

Lying? Whose lying? Where on the website is there a lie about
anything? From what I can tell, you've not taken the time to read the
documentation or post anything to the mailing list. You've just posted
jeremiads on the Python list.

Don't like the documentation? Ever volunteered to help out? Ever
posted any suggestions on the list or report a bug? Do you really
think that open source projects exists to serve you and meet your
standards? Do you think that free code and documentation just falls
like manna from heaven? Do you honestly think the two core developers
of SQLite have some secret agenda to deceive you or the world into
using SQLite?

But SQL isn't OO, it's relational. That means JOINing tables
together on a common field. In theory, due to the comparison
hierarchy, it is impossible to do JOINs with dynamic typing
since different types can never be equal. In practice, the type
affinity kluge trys to work around this but can't do anything
if the string doesn't look like an integer when a text field
attempts to JOIN to an interger field.

Unless you ensure that the correct types are put int the column to
begin with, which is entirely possible with SQLite, as I've already
demonstrated. And if that's just too much to bear, you can still do an
inner join by explicitly casting the two columns in the join
constraint to a common desired type. Want to know how? Read the
documentation.

 

[Dennis Lee Bieber]

But SQL isn't OO, it's relational. That means JOINing tables
together on a common field. In theory, due to the comparison
hierarchy, it is impossible to do JOINs with dynamic typing
since different types can never be equal. In practice, the type
affinity kluge trys to work around this but can't do anything
if the string doesn't look like an integer when a text field
attempts to JOIN to an interger field.

Pardon? Field or value? (Trying to join using a text /field/ against
an integer /field/ should have few matches, in my mind). If the data is
clean, the joins work... If the data has "noise", the noise probably
won't match anything -- but that is probably a sign that the data should
have been sanitized before insert/update.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Dennis Lee Bieber]

Just to let you (and everyone else know) there is a new SQLite book
out from APress that covers SQLite 3

Which I discovered about 10 minutes before finding this post <G>
More money to Amazon <G>
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[mensanator]

Implying? There's a solid word. Migrating data from SQLite to other
databases is no more difficult or easy than migrating data to any
other database. Do you think this is ever trivial? It's as hard or as
easy as you make it. No database can just take any schema and the data
you put in it and just magically convert that schema/data to
flawlessly work in any arbitrary database of your choosing. Some
databases have tools to help with this, but they still are not
perfect.

So, knowing that, would you agree that

<quote Python Library Reference 13.13>
If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy.
</quote>

is misleading if not outright untruthful?

Lying? Whose lying?

See above quote. And while you're at it, see the sqlite docs
about how the SQL Language Specification of static typing
is a bug.

Where on the website is there a lie about
anything? From what I can tell, you've not taken the time to read the
documentation or post anything to the mailing list. You've just posted
jeremiads on the Python list.

Don't like the documentation?

No, it's misleading and full of errors (this is the Python docs
I'm referring to).

Ever volunteered to help out?

That's what this thread was about, testing the waters.
No point making bug reports if I'm the one who's wrong.
But it turns out I'm not wrong, sqlite IS goofy and this
should be pointed out.

Ever posted any suggestions on the list or report a bug?

I'm still considering it. This thread has been very useful
towards that.

Do you really
think that open source projects exists to serve you and meet your
standards? Do you think that free code and documentation just falls
like manna from heaven?

But why does it have to be wrong? It's just as easy to get
things right. Isn't that your complaint, that if I read the sqlite
docs first, the riculous examples in the Python docs would
have made more sense? Why didn't the guy writing the Python
docs read the sqlite docs first?

Do you honestly think the two core developers
of SQLite have some secret agenda to deceive you or the world into
using SQLite?

Why do they claim that the SQL Language Specification of
static typing is a bug? That's simply a lie. Why do they claim
they've "fixed" it in a backwards compatible way? That's another
lie.

Why didn't they simply say they have an alternative to static
typing? Because part of the deception is to make people think
there is something wrong with static typing.

Unless you ensure that the correct types are put int the column to
begin with, which is entirely possible with SQLite, as I've already
demonstrated. And if that's just too much to bear, you can still do an
inner join by explicitly casting the two columns in the join
constraint to a common desired type. Want to know how? Read the
documentation.

And what do you get when you implement all these kluges?
A database that effectively is static typed. Do you still think
static typing is a bug?

 

[Fredrik Lundh]

So, knowing that, would you agree that

<quote Python Library Reference 13.13>
If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy.
</quote>

is misleading if not outright untruthful?

eh? if you've never migrated *from* SQLite to some other database, how
can *you* possibly know *anything* about how hard or easy it is?

</F>

 

[mensanator]

eh? if you've never migrated *from* SQLite to some other database, how
can *you* possibly know *anything* about how hard or easy it is?

Because I can extrapolate. I *know* before even trying it that
if I export all my data from a sqlite db to a csv file and then try
to import it into Access that there will be problems if the fields
aren't static typed.

That's one of the reasons why I was such a good test engineer.
I could anticipate problems the design engineers didn't think of
and I would deliberately provoke those problems during testing
and crash their hardware/software.

I wasn't very popular.

 

[Mike Owens]

So, knowing that, would you agree that

<quote Python Library Reference 13.13>
If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy.
</quote>

is misleading if not outright untruthful?

Not in the least.

If you know what you are doing from a database perspective (not just a
SQLite perspective), migrating data to another database is exactly
that -- relatively easy. That means, you may have to recreate or
modify your schema for the target database -- and this is true in ALL
databases. Native datatypes vary from system to system, and some
systems support user-defined data types, in which case your schema
will definitely have to be modified. How would you migrate a CIDR type
in PostgreSQL to a numeric field in Oracle? You have to work at it.

Next, as far as transferring you data, you most likely have to resort
to some delimited format, or INSERT statements, which is no different
than any other database.

So, I would call that relatively easy without a stretch, and
certainly no different than migrating data with any other database.

Really, how is this different than migrating data to/from any other database?

See above quote. And while you're at it, see the sqlite docs
about how the SQL Language Specification of static typing
is a bug.

Both of which have been addressed in detail. The above quote is not
even stretching the truth, and the latter fact is a deviation that
SQLite has every right to make because they, and not you, wrote the
software. Furthermore, it is very clearly stated on the website.

So how is that a lie?

No, it's misleading and full of errors (this is the Python docs
I'm referring to).

I didn't join this thread because of Python's documentation, and I've
made that clear. I am here because you are unjustly vilifying the
SQLite project.

That's what this thread was about, testing the waters.
No point making bug reports if I'm the one who's wrong.
But it turns out I'm not wrong, sqlite IS goofy and this
should be pointed out.

Then be a man and point it out on the SQLite mailing list, where you
can be called on it, rather than ranting about it here.

But why does it have to be wrong? It's just as easy to get
things right. Isn't that your complaint, that if I read the sqlite
docs first, the riculous examples in the Python docs would
have made more sense? Why didn't the guy writing the Python
docs read the sqlite docs first?

First, SQLite's approach is no more wrong than any other database's
deviation from the standard. Second, as I've said, I'm not here for
the Python issues. I think they'll get things sorted out in due time,
and people on this list have been very receptive to your feedback.

Why do they claim that the SQL Language Specification of
static typing is a bug? That's simply a lie. Why do they claim
they've "fixed" it in a backwards compatible way? That's another
lie.

It's not a lie at all. Are you incapable of comprehending the context
of that text? Do you not understand that it effectively says "This is
the way we do things. It's not in agreement with the SQL standard. We
know that, we are doing it this way, and here's how it works, take it
or leave it." And the whole bug in the SQL standard, if you can't
tell, is called humor.

Why didn't they simply say they have an alternative to static
typing?

They did. You couldn't understand that from the documentation?

Because part of the deception is to make people think
there is something wrong with static typing.

Yes, it's really an underhanded conspiracy designed to deceive and
mislead on a global scale. I can just see the developers sitting
around plotting:

"Hey, let's write some free software. Yeah let's give the code away
for free and not make a dime from it. Yeah, and then let's make up a
bunch of lies to make people want to use it, so we can continue to not
make a dime from it. And let's slander the SQL standard, and say all
sorts nasty things about it. Yeah, that's how we'll spend our nights
and weekends."

You really need to find some fault that will stick at this point, don't you?

They're really up to something.

And what do you get when you implement all these kluges?
A database that effectively is static typed.

Only if you want one. Otherwise, you have the freedom of dynamic
typing, which other databases don't afford. So, you in fact have more
freedom than you do than with databases that only offer strict typing.

Do you still think static typing is a bug?

Did I say this, ever? I am not the SQLite website.

I don't think either is a bug. Both are two different viewpoints used
to solve a particular problem. Dynamic typing and type affinity can be
incredibly useful, especially for prototyping and scripting languages.
I don't have to dump and reload my tables if I want to change a column
I am testing out. I just delete me data, reload it, and get my program
to store a different representation in the column. And when I need
strict typing, I simply declare a check constraint. So no, you can't
say that your particular need for static typing meets all criteria for
all developers using SQLite. And frankly, if SQLite doesn't meet my
requirements, rather than badmouthing SQLite, I use another database,
such as PostgreSQL.

And they are entirely correct in saying that you are provided the
facilities to implement strict typing. You are given five native
storage classes and the means to ensure that only data of those
classes is stored in columns. What's all the fuss?

Again, SQLite is completely up front about its approach to typing. I
still don't see how is it that anyone is lying to you.

 

[Steve Holden]

Because I can extrapolate. I *know* before even trying it that
if I export all my data from a sqlite db to a csv file and then try
to import it into Access that there will be problems if the fields
aren't static typed.

That's one of the reasons why I was such a good test engineer.
I could anticipate problems the design engineers didn't think of
and I would deliberately provoke those problems during testing
and crash their hardware/software.

I wasn't very popular.

Strange, that, what with your excess of personal charm and great powers
of diplomacy. People can be so touchy, can't they?

regards
Steve

 

[Fredrik Lundh]

Because I can extrapolate. I *know* before even trying it that
if I export all my data from a sqlite db to a csv file and then try
to import it into Access that there will be problems if the fields
aren't static typed.

that's just the old "C++/Java is better than Smalltalk/Python/Ruby"
crap. we've seen it before, and it's no more true when it comes from
you than when it comes from some Java head. people who've actually used
dynamic typing knows that it doesn't mean that all objects have random
types all the time.

That's one of the reasons why I was such a good test engineer.
I could anticipate problems the design engineers didn't think of
and I would deliberately provoke those problems during testing
and crash their hardware/software.

I wasn't very popular.

no wonder, if you kept running around telling your colleagues that they
were liars and crackpots and slanderers when things didn't work as you
expected. what's your current line of work, btw?

</F>

 

[A.M. Kuchling]

So, knowing that, would you agree that

<quote Python Library Reference 13.13>
If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy.
</quote>

is misleading if not outright untruthful?

As the original author of that sentence, I don't think it's either
misleading or untruthful; 'relatively easy' gives me wiggle room.
However, to fix your complaint, I've changed the paragraph to read:

SQLite is a C library that provides a lightweight disk-based
database that doesn't require a separate server process and allows
accessing the database using a nonstandard variant of the SQL query
language. Some applications can use SQLite for internal data
storage. It's also possible to prototype an application using SQLite
and then port the code to a larger database such as PostgreSQL or
Oracle.

Of course, if you accept Fredrik's reading of the SQL standard, the
word 'nonstandard' in the revised text is incorrect; SQLite is
compliant with the standard but in an unusual way. (But most readers
will interpret "nonstandard" as meaning "not like most other SQL
databases", so I'll let it stand.)

--amk

 

[mensanator]

that's just the old "C++/Java is better than Smalltalk/Python/Ruby"
crap. we've seen it before, and it's no more true when it comes from
you than when it comes from some Java head. people who've actually used
dynamic typing knows that it doesn't mean that all objects have random
types all the time.

No, it isn't the same old crap. When I define an Access field as
Double, I cannot insert a value such as ">200" or "ND" or "Yes".
I'm not saying static typing is better, just that migrating a dynamic
types to static types may cause difficulties that wouldn't be present
if it was static to static.

And if you call the "C++/Java is better than Smalltalk/Python/Ruby"
statement crap, why do you accept the statement that
"static typing is a bug in the SQL specification"? Isn't that
crap also?

no wonder, if you kept running around telling your colleagues that they
were liars and crackpots and slanderers when things didn't work as you
expected.

Nobody cared about that. What they cared about was my
reporting to their boss that the latest version of the software
was no better than the previous version who then had to figure
out how to explain to the customer that the improvement he
was promised didn't materialize and who then had to explain
to his boss why the customer still hadn't signed off on the
delivery and pay the bill.

what's your current line of work, btw?

Database manager for an a geotechnical consulting firm doing
environmental remediation.