473,747 Members | 2,736 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

sqlite3 in Python 2.5b1: my out-of-the-box experience

Apologies in advance if this is a bit bloggy, but I'd like to get
comments on whether I've lost the plot (or, more likely, failed to
acquire it) before I start reporting bugs etc.

From "What's new ...":
"""
# 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)""" )
"""

Point 1: Maybe that "timestamp" type for the first column should be
"date". More on this later.

Point 2: Maybe naming a column "date" wouldn't survive a real code
review :-)

Query results:
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000 001)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)

Point 3: Column 1 neither looks nor quacks like a datetime.dateti me
instance.

Point 4: Column 5 is quacking like a float, not a 'decimal'.

Point 5: There are no docs for sqlite3 in the Windows docs gadget that's
included in the 2.5b1 msi file [or the install didn't blow away the
2.5a2 docs gadget]. There are docs however at
http://docs.python.org/dev/lib/module-sqlite3.html

Looking for a way to get dates back instead of strings ... found
12.13.4.4 Default adapters and converters

Point 6: The example works (produces instances of datetime.* instead of
Unicode strings) but doesn't explain why *both* varieties of type
detection are specified in the connect() call.

Wrote a script to check out points 1 and 6:

8<--- start of script ---
import sqlite3, datetime

CREATE = """
create table stocks (
trans_date %s,
trans varchar,
symbol varchar,
qty decimal,
price decimal
)
"""

INSERT = """
insert into stocks
values ('2006-01-05','BUY','RHAT ',100,35.14)
"""

def test(col1type, detect_types):
conn = sqlite3.connect (":memory:", detect_types=de tect_types)
c = conn.cursor()
c.execute(CREAT E % col1type)
c.execute(INSER T)
c.execute('sele ct * from stocks')
for row in c:
print row
conn.close()

if __name__ == "__main__":
for ty in ['timestamp', 'date']:
for detective in [
0,
sqlite3.PARSE_C OLNAMES,
sqlite3.PARSE_D ECLTYPES,
sqlite3.PARSE_C OLNAMES | sqlite3.PARSE_D ECLTYPES,
]:
print "\ntest(%r, %d):" % (ty, detective)
test(ty, detective)
8<--- end of script ---

Results of running script:

test('timestamp ', 0):
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000 001)

test('timestamp ', 2):
(None, u'BUY', u'RHAT', 100, 35.140000000000 001)

test('timestamp ', 1):
(None, u'BUY', u'RHAT', 100, 35.140000000000 001)

test('timestamp ', 3):
(None, u'BUY', u'RHAT', 100, 35.140000000000 001)

test('date', 0):
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000 001)

test('date', 2):
(datetime.date( 2006, 1, 5), u'BUY', u'RHAT', 100, 35.140000000000 001)

test('date', 1):
(datetime.date( 2006, 1, 5), u'BUY', u'RHAT', 100, 35.140000000000 001)

test('date', 3):
(datetime.date( 2006, 1, 5), u'BUY', u'RHAT', 100, 35.140000000000 001)

Point 7: Type detection on a "timestamp" column causes None to be
retrieved after a date-only (yyyy-mm-dd) string is inserted. An
exception (or maybe defaulting the time part to 00:00:00) would be much
less astonishing.

Point 8: The test definitely doesn't use "... as [... date]" anywhere,
but PARSE_COLNAMES used in isolation appears to cause retrieval as a
datetime.date.

Point 9: IMHO the default should be to do both forms of type detection.

Comments on any of the above would be appreciated.

Cheers,
John
Jul 4 '06 #1
2 1972

John Machin wrote:
Apologies in advance if this is a bit bloggy, but I'd like to get
comments on whether I've lost the plot (or, more likely, failed to
acquire it) before I start reporting bugs etc.
These are not, in fact, bugs. One of SQLite's features is that it does
not enforce type, meaning that with the exception of INTEGER PRIMARY
KEY you can stick whatever you want into whatever field you want. The
philosophy of SQLite is that type checking is a "mis-feature" that goes
against some of the principles of the relational model.

Taking this into consideration, how would pysqlite handle opening a
database that mixes strings and integers in datetime fields? The short
answer is that it can't, so instead it is up to the developer to ensure
proper handling of type (sounds like python duck-typing, doesn't it?)

Jul 4 '06 #2
John Machin wrote:
Apologies in advance if this is a bit bloggy, but I'd like to get
comments on whether I've lost the plot (or, more likely, failed to
acquire it) before I start reporting bugs etc.
Please forward to Gerhard Haering <gh at ghaering.deif you still
think these are bugs.

Georg
Jul 4 '06 #3

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

Similar topics

5
1721
by: John Salerno | last post by:
I did a little experimentation with MySQL, and yesterday I was reading up on SQLite. Since they both use the SQL language, does this mean that the queries you write will be the same for both modules? I'm sure there are slight differences for how you connect to DBs, but since they both use the same DB API 2.0, and both use SQL, I was wondering how easily you could 'switch' them out if you needed to go from one to the other. (I know there...
66
7075
by: mensanator | last post by:
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:
2
4950
by: Josh | last post by:
Hi, I'm running into a problem when trying to create a view in my sqlite database in python. I think its a bug in the sqlite3 api that comes with python 2.5. This works as expected: conn = sqlite3.connect(':memory:') conn.execute("create table foo (a int,b int)") conn.execute('create view bar as select * from foo')
1
2650
by: Nader Emami | last post by:
L.S., I have to compile (install) locally Python 2.5, because I don't have 'root' permission. Besides I would use 'sqlite3' as a database for TurboGears/Django. I have installed 'sqlite3' somewhere on my Linux (/path/to/sqlite'). What do I have to do if I want to compile 'Python' with 'sqlite3'? I would appreciate if somebody tells me to solve this problem. With regards,
29
2369
by: dbhbarton | last post by:
Had a thought that's grown on me. No idea if it's original or not- too inexperienced in programming- but I guess there's no harm floating it out there. Python wins big on readability, and there's no doubt that context- dependent text formatting in IDEs (keywords, strings, comments etc) is a massive help too, therefore benefitting development and maintenance. This idea is in a similar vein, especially for when scripts grow large.
4
4241
by: Shawn McGrath | last post by:
Hi, I'm trying to expose a C++ class' internals to python via boost::python. I can do integer/boolean functions fine, but as soon as I do a string get/set it craps out. boost::python::class_<Entity, std::auto_ptr<pyEntity("Entity") //publics .def("isActive", &Entity::isActive) //bool .def("activate", &Entity::activate) //bool .def("deactivate", &Entity::deactivate) //bool //...
0
1197
by: datulaida | last post by:
Hi.. How to connect Python with Java. This is my Python program. i use jpype to connect Python with Java. from jpype import * startJVM("d:/Program Files/Java/jdk1.6.0/jre/bin/client/jvm.dll","-ea") jnavClass = JClass("Semantic") jnav = jnavClass() jnavClass.pilih_ayat("i go to school")
1
4344
by: datulaida | last post by:
Hi.. How to connect Python with Java. This is my Python program. i use jpype to connect Python with Java. Code: ( text ) 1. from jpype import * 2. startJVM("d:/Program Files/Java/jdk1.6.0/jre/bin/client/jvm.dll","-ea")
3
2972
by: milan_sanremo | last post by:
I have sqlite installed, but when I try to import sqlite3 I receive: Python 2.5.1 (r251:54863, Nov 3 2007, 02:54:36) on sunos5 Type "help", "copyright", "credits" or "license" for more information. Traceback (most recent call last): File "<stdin>", line 1, in <module> ImportError: No module named sqlite3 Yet:
1
1811
by: Gandalf | last post by:
Hi every one I'm looking for a good alternative db to replace sqlite I'm using pySQlite3, And I tried to translate very big database from Mysql to sqlite. I generated through PHP a python script that insert 200,000 records to my sqlite db and took me more then 5 hours and managed to insert only 100,000 records. I have almost million records so I need a better solution.
0
8818
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9522
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8233
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6067
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4588
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4860
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2771
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2203
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.