Rich Shepard wrote:
My copy of Mike Owen's excellent book arrived yesterday and I'm
trying to create and populate (with tables, data, and triggers) a new
database using the CLP.
I've never used -init but a CLI session to create a table is simply:
C:\>sqlite3 test
Loading resources from C:\Documents and
Settings\Martin/.sqliterc
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table t1(id INTEGER PRIMARY KEY, val TEXT);
sqlite> insert into t1(val) values ("Hello");
sqlite> insert into t1(val) values ("Hello");
sqlite> select * from t1;
id val
---------- ----------
1 Hello
2 Hello
sqlite> .out test.sql
sqlite> .dump
sqlite> .quit
The .out dumps the table as SQL command that can be read back in or
tweaked with an editor. It looks like:
BEGIN TRANSACTION;
CREATE TABLE t1(id INTEGER PRIMARY KEY, val TEXT);
INSERT INTO "t1" VALUES(1, 'Hello');
INSERT INTO "t1" VALUES(2, 'Hello');
COMMIT;
This is the SQL you'd need to get pysqlite (now included in Python 2.5
as sqlite3) to execute to create an identical database. In Python:
>>> import sqlite3
>>> con=sqlite3.Connection("c:/test1.db")
>>> csr=con.cursor()
>>> csr.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, val TEXT)");
<sqlite3.Cursor object at 0x00E3F230>
>>> csr.execute("INSERT INTO t1 VALUES(1, 'Hello');")
<sqlite3.Cursor object at 0x00E3F230>
>>> csr.execute("INSERT INTO t1(val) VALUES('Hello');")
<sqlite3.Cursor object at 0x00E3F230>
>>> csr.execute("select * from t1").fetchall()
[(1, u'Hello'), (2, u'Hello')]
>>>
Because I didn't explicitly start a transaction, pysqlite put all those
inserts in individual transactions, which will kills performance for a
lot of data. To get round that start your own transaction. There's a
csr.executemany(sql, dataset) variant which lets you insert lots of data
and pretty much has to be surrounded by an explicit transaction. Note
that the CLI requires a terminating semicolon but (as shown) Python's
sqlite3 doesn't care either way.
It's a bit brief, but I HTH
Martin Jenkins
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
