473,703 Members | 2,643 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Is it just me, or is Sqlite3 goofy?

Probably just me. I've only been using Access and SQL Server
for 12 years, so I'm sure my opinions don't count for anything.

I was, nevertheless, looking forward to Sqlite3. And now
that gmpy has been upgraded, I can go ahead and install
Python 2.5.

So I open the manual to Section 13.13 where I find the first
example of how to use Sqlite3:

<code>
conn = sqlite3.connect (':memory:')

c = conn.cursor()

# Create table
c.execute('''cr eate table stocks
(date timestamp, trans varchar, symbol varchar,
qty decimal, price decimal)''')

# Insert a row of data
c.execute("""in sert into stocks
values ('2006-01-05','BUY','RHAT ',100,35.14)""" )
</code>

Seems pretty simple, yet I was befuddled and bewildered
by this example. So much so that I added a query to see exactly
what was going on.

<code>
# added by me
c.execute('sele ct * from stocks')
d = c.fetchone()
for t in d:
print type(t),t
</code>

Original code - what's wrong with this picture?
<type 'unicode'2006-01-05
<type 'unicode'BUY
<type 'unicode'RHAT
<type 'int'100
<type 'float'35.14

Why is the date returning as a string? Aren't the built in
converters supposed to handle that? Yes, if you enable
detect_types.

Added detect_types=sq lite3.PARSE_DEC LTYPES

Traceback (most recent call last):
File "C:\Python25\sq lite_first_exam ple.py", line 30, in <module>
c.execute('sele ct * from stocks')
File "C:\Python25\li b\sqlite3\dbapi 2.py", line 66, in
convert_timesta mp
datepart, timepart = val.split(" ")
ValueError: need more than 1 value to unpack

Aha, that explains why they weren't enabled.

This failed because
- the value inserted was wrong format?
- and the builtin converter can't split it cuz it has no spaces?
when it worked it was because
- detect_types was not set, so converter not invoked when queried?

Yes, the format in the example was datetime.date and the field type
should have been cast [date], not [timestamp] which needs HH:MM:SS
for the converter to work properly (but only if detect_types
enabled).

If a correct format was inserted, converter would have worked

<type 'datetime.datet ime'2006-09-04 13:30:00
<type 'unicode'BUY
<type 'unicode'RHAT
<type 'int'100
<type 'float'35.14

Or, had the field been properly cast as [date] instead of [timestamp]
it would also have worked.

<type 'datetime.date' 2006-09-04
<type 'unicode'BUY
<type 'unicode'RHAT
<type 'int'100
<type 'float'35.14

Ah, this now partly explains the original result, since detect_types
is off by default, the field cast, not being a native sqlite3 type
was ignored and the data queried back as TEXT.

<type 'unicode'2006-09-04
<type 'unicode'BUY
<type 'unicode'RHAT
<type 'int'100
<type 'float'35.14

Ok, next issue, what the fuck are [varchar] and [decimal]?
They are certainly not Sqlite3 native types. If they are
user defined types, where are the converters and adapters?
Does Sqlite3 simply ignore a cast that isn't registered?

Note that although both qty and price were cast as [decimal]
they queried back as int and float.

Note also that it's "obvious" from the query example on page 13.13
that the example is FUBAR
- the date is a unicode string, not a datetime.date type
- the price is binary floating point, not decimal

<quote>
This example uses the iterator form:
>>c = conn.cursor()
c.execute('se lect * from stocks order by price')
for row in c:
... print row
...
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000 001)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
</quote>

Here we have an example of things apparently working
for the wrong reason. A classic example of the programmer
who *thinks* he knows how it works because he wrote it.
This kind of sloppiness wouldn't last 5 minutes in a production
environment.

But why did Sqlite3 make qty an int and price a float?
Hard to say since THE FURTHER EXAMPLES IN THE DOCS don't
even bother to cast the field types. I guess I'm supposed
to guess how things are supposed to work. Can I trust that
default settings will be what I want?

Ha! Can I trust the baby with a hammer?

First, note that the script example in section 13.13.3

<quote>
import sqlite3

con = sqlite3.connect (":memory:")
cur = con.cursor()
cur.executescri pt("""
create table person(
firstname,
lastname,
age
);

create table book(
title,
author,
published
);

insert into book(title, author, published)
values (
'Dirk Gently''s Holistic Detective Agency
'Douglas Adams',
1987
);
""")
</quote>

contains not one but TWO syntax errors! A single quote after
the word Agency is missing as is the comma that should be at
the end of that line. Seems that no one actually ever tried
this example.

That's easily fixed. But I was curious about why the fields
don't have type casts. After the previous debacle and
knowing that this code was never tested, I am not going to
assume it works. Better add a query to make sure.

cur.execute("se lect title, author, published from book")
d = cur.fetchall()
for i in d: print i
print

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)

Ok, so if not cast, the fields must default (and probably also when
a cast is made that hasn't been defined).

But watch this: being clueless (but not stupid) is a gift I have
for troubleshooting . I tried (incorrectly) to insert another record:

cur.execute("in sert into book(title, author, published) values ('Dirk
Gently''s Holistic Detective Agency','Dougla s Adams','1987')" )

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)
(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', u'1987')

Uhh...how can a database have a different field type for each record?

Simple, without a cast when the table is created, the field type is
whatever you insert into it. That's how the default must work,
each record has a data structure independent of every other record!

Wow. Just think of the kind of bugs *that* must cause.

Bugs?

Here's MY example, creating a Cartesian Product

<code>
import sqlite3
letters = [(2,),('10',),(' 20',),(200,)]
con = sqlite3.connect (":memory:")
con.text_factor y = str
con.execute("cr eate table letter(c integer)")
con.executemany ("insert into letter(c) values (?)", letters)
print 'Queried: ',
for row in con.execute("se lect c from letter"):
print row,
print
print
print 'Sorted: ',
for row in con.execute("se lect c from letter order by c"):
print row[0],
print
print
print 'Cartesian Product: ',
for row in con.execute("se lect a.c, b.c, c.c from letter as a, letter
as b, letter as c"):
print row[0]+row[1]+row[2],
</code>

Note that the list of data to be inserted contains both strings and
ints. But because the field was correctly cast as [integer], Sqlite3
actually stored integers in the db. We can tell that from how the
"order by" returned the records.

Queried: (2,) (10,) (20,) (200,)

Sorted: 2 10 20 200

Cartesian Product: 6 14 24 204 14 22 32 212 24 32 42 222 204 212
222 402 14 22 32 212 22 30 40 220 32 40 50 230 212 220 230 410 24
32 42 222 32 40 50 230 42 50 60 240 222 230 240 420 204 212 222
402 212 220 230 410 222 230 240 420 402 410 420 600

Because if I cast them as [text] the sort order changes (and my
Cartesian Product becomes concatenation instead of summation).

Queried: ('2',) ('10',) ('20',) ('200',)

Sorted: 10 2 20 200

Cartesian Product: 222 2210 2220 22200 2102 21010 21020 210200
2202 22010 22020 220200 22002 220010 220020 2200200 1022 10210
10220 102200 10102 101010 101020 1010200 10202 102010 102020
1020200 102002 1020010 1020020 10200200 2022 20210 20220 202200
20102 201010 201020 2010200 20202 202010 202020 2020200 202002
2020010 2020020 20200200 20022 200210 200220 2002200 200102
2001010 2001020 20010200 200202 2002010 2002020 20020200 2002002
20020010 20020020 200200200

But if I omit the cast altogether, then the db stores the input
exactly as it was inserted, so the c field contains both
text and integers wreaking havoc with my sort order, making
records un-queryable using "where" and causing my Cartesian
Product to crash.

Queried: (2,) ('10',) ('20',) (200,)

Sorted: 2 200 10 20

Cartesian Product: 6

Traceback (most recent call last):
File "C:\Python25\us er\sqlite_test2 .py", line 225, in <module>
print row[0]+row[1]+row[2],
TypeError: unsupported operand type(s) for +: 'int' and 'str'

Yeah, I know, I've heard it before.

"This behavior is by design."

It's still fuckin' goofy.

Sep 5 '06 #1
66 7064
In <11************ **********@p79g 2000cwp.googleg roups.com>,
me********@aol. com wrote:
But watch this: being clueless (but not stupid) is a gift I have
for troubleshooting . I tried (incorrectly) to insert another record:

cur.execute("in sert into book(title, author, published) values ('Dirk
Gently''s Holistic Detective Agency','Dougla s Adams','1987')" )

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)
(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', u'1987')

Uhh...how can a database have a different field type for each record?

Simple, without a cast when the table is created, the field type is
whatever you insert into it. That's how the default must work,
each record has a data structure independent of every other record!

Wow. Just think of the kind of bugs *that* must cause.

Bugs?
It's not a bug, it's a feature. And answered as third point in the FAQ:

http://www.sqlite.org/faq.html#q3

I think your whole experience is based on it. Live with it or use a
real RDBMS.

If you are so fond of static typing, why are you using Python in the first
place? Just see it as consistency -- dynamically typed language →
dynamically typed DB columns. ;-)

Ciao,
Marc 'BlackJack' Rintsch
Sep 5 '06 #2
me********@aol. com wrote:
Probably just me. I've only been using Access and SQL Server
for 12 years, so I'm sure my opinions don't count for anything.
[...]
>
Ok, next issue, what the fuck are [varchar] and [decimal]?
[..]
>
It's still fuckin' goofy.
Language ...

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

Sep 5 '06 #3
me********@aol. com wrote:
Probably just me. I've only been using Access and SQL Server
for 12 years, so I'm sure my opinions don't count for anything.
SQLite never pretended to be a full-blown RDBMS - just a lightweight
simple embedded database as SQL-compliant as possible. In it's category,
it beats Access and MySQL hands down. Now if you want a real RDBMS,
you've just failed to choose the right tool. May I suggest PostgreSQL ?

(snip useless rant)

--
bruno desthuilliers
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'o****@xiludom. gro'.split('@')])"
Sep 5 '06 #4

Marc 'BlackJack' Rintsch wrote:
In <11************ **********@p79g 2000cwp.googleg roups.com>,
me********@aol. com wrote:
But watch this: being clueless (but not stupid) is a gift I have
for troubleshooting . I tried (incorrectly) to insert another record:

cur.execute("in sert into book(title, author, published) values ('Dirk
Gently''s Holistic Detective Agency','Dougla s Adams','1987')" )

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)
(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', u'1987')

Uhh...how can a database have a different field type for each record?

Simple, without a cast when the table is created, the field type is
whatever you insert into it. That's how the default must work,
each record has a data structure independent of every other record!

Wow. Just think of the kind of bugs *that* must cause.

Bugs?

It's not a bug, it's a feature. And answered as third point in the FAQ:

http://www.sqlite.org/faq.html#q3
Oh, so it is "This behaviour is by design".
>
I think your whole experience is based on it.
But shouldn't a significant feature like that be explained
in the Python manuals? Why should I go dig up Sqlite
FAQs to learn what should have been in the manuals?
Live with it or use a real RDBMS.
I don't mind living with it as long as it's documented.
>
If you are so fond of static typing, why are you using Python in the first
place? Just see it as consistency -- dynamically typed language →
dynamically typed DB columns. ;-)
Did you miss this statement in Section 13.13?

"If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy. "

Obviously, this is a new useage of "relatively easy" with which
I have been previously unaware.

Ciao,
Marc 'BlackJack' Rintsch
Sep 5 '06 #5
>I think your whole experience is based on it.
>But shouldn't a significant feature like that be explained in the
Python manuals? Why should I go dig up Sqlite FAQs to learn what
should have been in the manuals?
I don't know, but I will take a stab at a plausible explanation. First,
sqlite support has only been in Python for a month or three. Its first
official unveiling will be when 2.5 is released. Second, it's common when
wrapping functionality into Python to rely on the documentation for the
thing being wrapped. The thinner the wrapper, the more you tend to rely on
the underlying documentation. Also, the more functionally rich the thing
you've wrapped, the more you rely on the underlying documentation. I
wouldn't be at all surprised if the pysqlite author operated under that
assumption. That the Python developers didn't pick up on the issue is not
surprising. I'm not sure how many of them are (py)sqlite users, probably
relatively few.

Skip
Sep 5 '06 #6

sk**@pobox.com wrote:
I think your whole experience is based on it.
>But shouldn't a significant feature like that be explained in the
>Python manuals? Why should I go dig up Sqlite FAQs to learn what
>should have been in the manuals?

I don't know, but I will take a stab at a plausible explanation. First,
sqlite support has only been in Python for a month or three. Its first
official unveiling will be when 2.5 is released.
Although possibly too late for the final release, now would be a
good time to straighten out the documentation.
Second, it's common when
wrapping functionality into Python to rely on the documentation for the
thing being wrapped. The thinner the wrapper, the more you tend to rely on
the underlying documentation. Also, the more functionally rich the thing
you've wrapped, the more you rely on the underlying documentation. I
wouldn't be at all surprised if the pysqlite author operated under that
assumption.
Ok, that's certainly plausible. But it's not an excuse. The thinner the
documentation, the greater the emphasis should be made to point
the reader to a more adequate source. Simply listing the Sqlite home
page at the bottom of the page is hardly good enough. It should be
explicitly stated in bold letters that the reader should go read the
Sqlite FAQ because it radically differs from *real* databases and
provide a seperate link to it in the body of the documentation.
That the Python developers didn't pick up on the issue is not
surprising. I'm not sure how many of them are (py)sqlite users, probably
relatively few.
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.

What I'll do is re-format my rant, suggest how *I* would do the
documentation, fix the errors I found in the examples and send it
off to the Python bug tracking as suggested in the manuals.

How's that as a plan?
>
Skip
Sep 5 '06 #7
>What I'll do is re-format my rant, suggest how *I* would do the
documentatio n, fix the errors I found in the examples and send it off
to the Python bug tracking as suggested in the manuals.
>How's that as a plan?
That's fine. Reformat your rant as a documentation bug report on
SourceForge:

http://sourceforge.net/projects/python

If you mention specific sqlite documentation urls you think should be
referenced in the pysqlite docs it would make it easier to accept.

Skip

Sep 5 '06 #8
me********@aol. com wrote:
sk**@pobox.com wrote:
>>>>I think your whole experience is based on it.
> >But shouldn't a significant feature like that be explained in the
>Python manuals? Why should I go dig up Sqlite FAQs to learn what
>should have been in the manuals?

I don't know, but I will take a stab at a plausible explanation. First,
sqlite support has only been in Python for a month or three. Its first
official unveiling will be when 2.5 is released.


Although possibly too late for the final release, now would be a
good time to straighten out the documentation.
And you would be the best person to do it, since you're teh one this has
bitten in the tender parts.
>
>>Second, it's common when
wrapping functionality into Python to rely on the documentation for the
thing being wrapped. The thinner the wrapper, the more you tend to rely on
the underlying documentation. Also, the more functionally rich the thing
you've wrapped, the more you rely on the underlying documentation. I
wouldn't be at all surprised if the pysqlite author operated under that
assumption.


Ok, that's certainly plausible. But it's not an excuse. The thinner the
documentation, the greater the emphasis should be made to point
the reader to a more adequate source. Simply listing the Sqlite home
page at the bottom of the page is hardly good enough. It should be
explicitly stated in bold letters that the reader should go read the
Sqlite FAQ because it radically differs from *real* databases and
provide a seperate link to it in the body of the documentation.
Whoa, there! This isn't commercial software we are talking about. While
I appreciate the need to continually better Python's documentation, the
"should" implies a moral imperative that the (volunteer) developers are
unikely to find compelling.
>
>>That the Python developers didn't pick up on the issue is not
surprising. I'm not sure how many of them are (py)sqlite users, probably
relatively few.


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.

What I'll do is re-format my rant, suggest how *I* would do the
documentation, fix the errors I found in the examples and send it
off to the Python bug tracking as suggested in the manuals.

How's that as a plan?
That's the ticket. Great idea. Changes to the documentation can be
suggested in plain ASCII, you don't have to grok the LaTeX markup.

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

Sep 6 '06 #9
me********@aol. com wrote:
sk**@pobox.com wrote:
>>>>I think your whole experience is based on it.
> >But shouldn't a significant feature like that be explained in the
>Python manuals? Why should I go dig up Sqlite FAQs to learn what
>should have been in the manuals?

I don't know, but I will take a stab at a plausible explanation. First,
sqlite support has only been in Python for a month or three. Its first
official unveiling will be when 2.5 is released.


Although possibly too late for the final release, now would be a
good time to straighten out the documentation.
And you would be the best person to do it, since you're the one this has
bitten in the tender parts.
>
>>Second, it's common when
wrapping functionality into Python to rely on the documentation for the
thing being wrapped. The thinner the wrapper, the more you tend to rely on
the underlying documentation. Also, the more functionally rich the thing
you've wrapped, the more you rely on the underlying documentation. I
wouldn't be at all surprised if the pysqlite author operated under that
assumption.


Ok, that's certainly plausible. But it's not an excuse. The thinner the
documentation, the greater the emphasis should be made to point
the reader to a more adequate source. Simply listing the Sqlite home
page at the bottom of the page is hardly good enough. It should be
explicitly stated in bold letters that the reader should go read the
Sqlite FAQ because it radically differs from *real* databases and
provide a seperate link to it in the body of the documentation.
Whoa, there! This isn't commercial software we are talking about. While
I appreciate the need to continually better Python's documentation, the
"should" implies a moral imperative that the (volunteer) developers are
unlikely to find compelling.
>
>>That the Python developers didn't pick up on the issue is not
surprising. I'm not sure how many of them are (py)sqlite users, probably
relatively few.


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.

What I'll do is re-format my rant, suggest how *I* would do the
documentation, fix the errors I found in the examples and send it
off to the Python bug tracking as suggested in the manuals.

How's that as a plan?
That's the ticket. Great idea. Changes to the documentation can be
suggested in plain ASCII, you don't have to grok the LaTeX markup.

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

Sep 6 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.