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

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

Looking for a way to get dates back instead of strings ... found 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

if __name__ == "__main__":
for ty in ['timestamp', 'date']:
for detective in [
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

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

Comments on any of the above would be appreciated.

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.

Jul 4 '06 #3

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

Similar topics

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...
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:
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')
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,
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.
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 //...
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")
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")
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:
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.
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,...
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...
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...
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...
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();...
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...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
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.