~ubuntu-branches/ubuntu/lucid/python-apsw/lucid

« back to all changes in this revision

Viewing changes to apsw.html

Committer: Bazaar Package Importer
Author(s): Joel Rosdahl
Date: 2008-07-07 20:24:11 UTC
mfrom: (1.1.5 upstream) (3.1.1 lenny)
Revision ID: james.westby@ubuntu.com-20080707202411-612ylo5p89y9ilvi

Tags: 3.5.9-r2-1

* New upstream release.
* Build dependency on libsqlite3-dev 3.5.9.
* Updated Standards-Version to 3.8.0.

files modified:
apsw.c

apsw.html

apswversion.h

debian/changelog

debian/control

mingwsetup.bat

setup.py

statementcache.c

tests.py

traceback.c

Show diffs side-by-side

added added

removed removed

apsw.html

<head>

"HTML Tidy for Linux (vers 1 September 2005), see www.w3.org" />

<title>APSW - Another Python SQLite Wrapper</title>

/*<![CDATA[*/

body {

font-family: verdana, arial, helvetica, sans-serif;

margin: 20px;

background-color: #ffcc99;

padding: 7px;

}

/*]]>*/

</style>

}

.pynumber { color: #0080C0;}

.pyoperator { color: #0000C0;}

.pystring { color: #004080;}

.pycomment { color: #008000;}

.pyname { color: #000000;}

.pyerror { color: #FF8080;}

.pykeyword { color: #C00000;}

.pytext { color: #000000;}

.pyoutput { color: #0000FF;}

/*]]>*/

</style>

</head>

<body>

<h1 class="notoc">APSW - Another Python SQLite Wrapper</h1>

apsw-3.3.13-r1 18th February 2007

APSW provides an SQLite 3 wrapper that provides the thinnest layer over

<a href="http://www.sqlite.org">SQLite 3</a> possible. Everything you can do

from the <a href="http://www.sqlite.org/capi3ref.html">C API</a> to SQLite 3,

you can do from Python. Although APSW looks vaguely similar to the <a href=

"http://www.python.org/peps/pep-0249.html">DBAPI</a>, it is <a href=

"#dbapinotes">not compliant</a> with that API and instead works the way

SQLite 3 does. (<a href="http://www.pysqlite.org">pysqlite</a> is DBAPI

compliant - <a href="#pysqlitediffs">differences between apsw and pysqlite

2</a>).

Important changes in 3.3.10 release You must explicitly call

<a href="#Connection_class">close()</a> on Connection objects. If you do

not call close then a detailed error message will be printed when

Connection's destructor runs and cleanup will not happen of that

connection. This is because it is not possible to raise Exceptions in

destructors (actually it is, but Python just calls the destructor again

which can result in an infinite loop). I did try to work around this, but

with things like no control over which thread destructors run in, virtual

tables, triggers, incomplete statements and SQLite's transaction control,

it becomes impossible to cleanly close a connection if bits of code are

returning errors. Using the close() method allows errors to be returned and

it can be called again once they are fixed.

All strings returned by APSW are now in Unicode. Previously strings that

consisted only of ASCII characters where returned as the string type and

other strings were returned as the unicode type. This did allow easy usage

with other components that knew nothing of Unicode. However it could make

Unicode aware software do more work.

</blockquote>

apsw-3.5.9-r2 6th July 2008 (Use with SQLite 3.5.9 or later, Python 2.3 or later including

Python 3)

APSW provides an SQLite 3 wrapper that provides the thinnest layer over <a href="http://www.sqlite.org">SQLite

3</a> possible. Everything you can do from the <a href="http://www.sqlite.org/c3ref/intro.html">C API</a> to SQLite

3, you can do from Python. Although APSW looks vaguely similar to the <a href=

"http://www.python.org/peps/pep-0249.html">DBAPI</a>, it is <a href="#dbapinotes">not compliant</a> with that API and

instead works the way SQLite 3 does. (<a href="http://www.pysqlite.org">pysqlite</a> is DBAPI compliant - <a href=

"#pysqlitediffs">differences between apsw and pysqlite 2</a>).

<h1 class="notoc">Table of contents</h1>

<tr>

</ul>

</li>

<li><a href="#SQLiteVersionCompatibilityAndBenchmarking">SQLite

version compatibility and benchmarking</a></li>

<li><a href="#SQLiteVersionCompatibilityAndBenchmarking">SQLite version compatibility and

benchmarking</a></li>

<li><a href="#Example">Example</a></li>

<li><a href="#Building">Building</a></li>

<li>

<a href="#Building">Building</a>

<ul>

<li><a href="#Finding">Finding SQLite 3</a></li>

<li><a href="#Recommended">Recommended</a></li>

</ul>

</li>

<li>

<a href="#APIReference">API Reference</a>

<li><a href="#Connection_class">Connection class</a></li>

<li><a href="#Cursor">Cursor class</a></li>

<li><a href="#VirtualTables">Virtual Tables</a></li>

102

<a href="#Exceptions">Exceptions</a>

103

104

<ul>

105

<li><a href="#augmentedstacktraces">Augmented stack

106

traces</a></li>

100

<li><a href="#augmentedstacktraces">Augmented stack traces</a></li>

107

101

</ul>

108

102

</li>

109

103

143

137

144

138

<li><a href="#dpapitypes">Type objects</a></li>

145

139

146

<li><a href="#dbapiextensions">Optional DB API

147

Extensions</a></li>

140

<li><a href="#dbapiextensions">Optional DB API Extensions</a></li>

148

141

</ul>

149

142

</li>

150

143

156

149

<a href="#verhistory">Version History</a>

157

150

158

151

<ul>

152

153

154

155

159

156

160

157

161

158

195

192

You can download this release in source or binary form.

196

193

197

194

<ul>

198

<li><a href=

199

"http://initd.org/pub/software/pysqlite/apsw/3.3.13-r1/apsw-3.3.13-r1.zip">apsw-3.3.13-r1.zip</a>

200

(source)</li>

201

202

<li><a href=

203

"http://initd.org/pub/software/pysqlite/apsw/3.3.13-r1/apsw-3.3.13-r1.win32-py2.3">

204

apsw-3.3.13-r1.win32-py2.3.exe</a> (Windows Python 2.3)</li>

205

206

<li><a href=

207

"http://initd.org/pub/software/pysqlite/apsw/3.3.13-r1/apsw-3.3.13-r1.win32-py2.4">

208

apsw-3.3.13-r1.win32-py2.4.exe</a> (Windows Python 2.4)</li>

209

210

<li><a href=

211

"http://initd.org/pub/software/pysqlite/apsw/3.3.13-r1/apsw-3.3.13-r1.win32-py2.5">

212

apsw-3.3.13-r1.win32-py2.5.exe</a> (Windows Python 2.5)</li>

195

196

<li><a href="http://apsw.googlecode.com/files/apsw-3.5.9-r2.zip">apsw-3.5.9-r2.zip</a> (source)</li>

197

198

199

(Windows Python 2.3)</li>

200

201

202

(Windows Python 2.4)</li>

203

204

205

(Windows Python 2.5)</li>

213

206

</ul>

214

207

215

Some Linux distributions also have packages. Debian users can grab the

216

package <a href="http://packages.debian.org/python-apsw">python-apsw</a>.

217

Gentoo users can grab the package <a href=

208

Some Linux distributions also have packages. Debian/Ubuntu users can grab the package <a href=

209

"http://packages.debian.org/python-apsw">python-apsw</a>. Gentoo users can grab the package <a href=

218

210

"http://www.gentoo-portage.com/dev-python/apsw">dev-python/apsw</a>.

219

211

220

212

<h2><a name="Sourcecode" id="Sourcecode">Source code control</a></h2>

221

213

222

The source is controlled by Subversion accessible as <a href=

223

"http://initd.org/svn/pysqlite/apsw/trunk">http://initd.org/svn/pysqlite/apsw/trunk</a>

224

225

<h1><a name="SQLiteVersionCompatibilityAndBenchmarking" id=

226

"SQLiteVersionCompatibilityAndBenchmarking">SQLite version compatibility and

227

benchmarking</a></h1>

228

229

APSW binds to the C interface of SQLite. That interface is stable for each

230

major version of SQLite (ie the C interface for any SQLite 3.x is stable, but

231

SQLite 4.x would be an incompatible change). Consequently you can use APSW

232

against any revision of SQLite with the same major version number. There are

233

small enhancements to the C api over time, and APSW adds support for them as

234

appropriate. The version number of APSW covers the version these enhancements

235

were added. The vast majority of changes to SQLite are in the SQL syntax and

236

engine. Those will be picked up with any version of APSW. The one exception

237

to this is experimental features in SQLite which may change API between

238

revisions. Consequently you will need to turn them off if you want to work

239

against a variety of versions of SQLite (see EXPERIMENTAL in

240

<code>setup.py</code>). I strongly recommend you embed the SQLite library

241

into APSW which will put you in control of versions.

242

243

Before you do any benchmarking with APSW or other ways of accessing

244

SQLite, you must understand how and when SQLite does transactions. See

245

section 7.0, Transaction Control At The SQL Level of <a href=

246

"http://sqlite.org/lockingv3.html">sqlite.org/lockingv3.html</a>. APSW

247

does not alter SQLite's behaviour with transactions. Some access layers

248

try to interpret your SQL and manage transactions behind your back, which may

249

or may not work well with SQLite also do its own transactions. You should

250

always manage your transactions yourself. For example to insert 1,000 rows

251

wrap it in a single transaction else you will have 1,000 transactions. The

252

best clue that you have one transaction per statement is having a maximum of

253

60 statements per second. You need two drive rotations to do a transaction -

254

the data has to be committed to the main file and the journal - and 7200 RPM

255

drives do 120 rotations a second. On the other hand if you don't put in the

256

transaction boundaries yourself and get more than 60 statements a second,

257

then your access mechanism is silently starting transactions for you. This

258

topic also comes up fairly frequently in the SQLite mailing list

259

archives.

214

The source is controlled by Subversion documented at <a href=

215

"http://code.google.com/p/apsw/source/checkout">http://code.google.com/p/apsw/source/checkout</a>

216

217

<h1><a name="SQLiteVersionCompatibilityAndBenchmarking" id="SQLiteVersionCompatibilityAndBenchmarking">SQLite version

218

compatibility and benchmarking</a></h1>

219

220

APSW binds to the C interface of SQLite. That interface is stable for each major version of SQLite (ie the C

221

interface for any SQLite 3.x is stable, but SQLite 4.x would be an incompatible change). Consequently you can use

222

APSW against any revision of SQLite with the same major version number. There are small enhancements to the C api

223

over time, and APSW adds support for them as appropriate. The version number of APSW covers the version these

224

enhancements were added. The vast majority of changes to SQLite are in the SQL syntax and engine. Those will be

225

picked up with any version of APSW. The one exception to this is experimental features in SQLite which may change API

226

between revisions. Consequently you will need to turn them off if you want to work against a variety of versions of

227

SQLite (see EXPERIMENTAL in <code>setup.py</code>). I strongly recommend you embed the SQLite library into APSW which

228

will put you in control of versions.

229

230

Before you do any benchmarking with APSW or other ways of accessing SQLite, you must understand how and when

231

SQLite does transactions. See section 7.0, Transaction Control At The SQL Level of <a href=

232

"http://sqlite.org/lockingv3.html">sqlite.org/lockingv3.html</a>. APSW does not alter SQLite's behaviour with

233

transactions. Some access layers try to interpret your SQL and manage transactions behind your back, which may or

234

may not work well with SQLite also do its own transactions. You should always manage your transactions yourself. For

235

example to insert 1,000 rows wrap it in a single transaction else you will have 1,000 transactions. The best clue

236

that you have one transaction per statement is having a maximum of 60 statements per second. You need two drive

237

rotations to do a transaction - the data has to be committed to the main file and the journal - and 7200 RPM drives

238

do 120 rotations a second. On the other hand if you don't put in the transaction boundaries yourself and get more

239

than 60 statements a second, then your access mechanism is silently starting transactions for you. This topic also

240

comes up fairly frequently in the SQLite mailing list archives.

260

241

261

242

<h1><a name="Example" id="Example">Example</a></h1>

262

243

263

This is an example of how to use apsw, and also demonstrates all the

264

features.

244

This is an example of how to use apsw, and also demonstrates all the features.

265

245

266

246

267

247

<pre>

268

248

269

<font color=

270

"#C00000">import os, sys<font color=

271

"#0000C0">, time

272

import apsw

249

import <span class=

250

"pyname">os, sys<span class=

251

"pyoperator">, time

252

import apsw

273

253

274

###

254

###

275

255

### Check we have the expected version of apsw and sqlite

276

256

###

277

257

278

print <font color=

279

"#004080">"Using APSW file"<font color=

280

"#0000C0">,apsw.__file__ # from the extension module

281

print <font color=

282

"#004080">" APSW version"<font color=

283

"#0000C0">,apsw.apswversion<font color=

284

"#0000C0">() # from the extension module

285

print <font color=

286

"#004080">" SQLite version"<font color=

287

"#0000C0">,apsw.sqlitelibversion() # from the sqlite library code

288

print <font color=

289

"#004080">" SQLite version"<font color=

290

"#0000C0">,apsw.SQLITE_VERSION_NUMBER # from the sqlite header file at compile time

258

print " Using APSW file"<span class=

259

"pyoperator">,apsw.<span class=

260

"pyname">__file__ # from the extension module

261

print " APSW version"<span class=

262

"pyoperator">,apsw.<span class=

263

"pyname">apswversion() <span class=

264

"pycomment"># from the extension module

265

print " SQLite lib version"<span class=

266

"pyoperator">,apsw.<span class=

267

"pyname">sqlitelibversion() <span class=

268

"pycomment"># from the sqlite library code

269

print "SQLite header version"<span class=

270

"pyoperator">,apsw.<span class=

271

"pyname">SQLITE_VERSION_NUMBER # from the sqlite header file at compile time

291

272

292

Using APSW file /space/apsw/apsw.so

293

APSW version 3.3.13-r1

294

SQLite version 3.3.13

295

SQLite version 3003013

296

297

###

273

Using APSW file /space/apsw/apsw.so

274

APSW version 3.5.9-r2

275

SQLite lib version 3.5.9

276

SQLite header version 3005009

277

278

###

298

279

### Opening/creating database

299

280

###

300

281

301

if os<font color=

302

"#0000C0">.path.exists<font color=

303

"#0000C0">("dbfile"<font color=

304

"#0000C0">): os.remove<font color=

305

"#0000C0">("dbfile")

306

connection=apsw<font color=

307

"#0000C0">.Connection(<font color=

308

"#004080">"dbfile")

309

cursor=connection<font color=

310

"#0000C0">.cursor()

282

if os.<span class=

283

"pyname">path.exists<span class=

284

"pyoperator">("dbfile"): <span class=

285

"pyname">os.remove<span class=

286

"pyoperator">("dbfile")

287

connection=<span class=

288

"pyname">apsw.Connection<span class=

289

"pyoperator">("dbfile")

290

cursor=<span class=

291

"pyname">connection.cursor<span class=

292

"pyoperator">()

311

293

312

###

294

###

313

295

### simple statement

314

296

###

315

297

316

cursor.execute<font color=

317

"#0000C0">(<font color=

318

"#004080">"create table foo(x,y,z)")

298

cursor.<span class=

299

"pyname">execute(<span class=

300

"pystring">"create table foo(x,y,z)")

319

301

320

###

302

###

321

303

### multiple statements

322

304

###

323

305

324

cursor.execute<font color=

325

"#0000C0">(<font color=

326

"#004080">"insert into foo values(1,2,3); create table bar(a,b,c) ; insert into foo values(4, 'five', 6.0)")

306

cursor.<span class=

307

"pyname">execute(<span class=

308

"pystring">"insert into foo values(1,2,3); create table bar(a,b,c) ; insert into foo values(4, 'five', 6.0)")

327

309

328

###

310

###

329

311

### iterator

330

312

###

331

313

332

for x<font color=

333

"#0000C0">,y,z <font color=

334

"#C00000">in cursor.execute<font color=

335

"#0000C0">(<font color=

336

"#004080">"select x,y,z from foo"):

337

print cursor<font color=

338

"#0000C0">.getdescription() <font color=

339

"#008000"># shows column names and declared types

340

print x<font color=

341

"#0000C0">,y,z

314

for x,<span class=

315

"pyname">y,z <span class=

316

"pykeyword">in cursor.<span class=

317

"pyname">execute(<span class=

318

"pystring">"select x,y,z from foo"):

319

print cursor<span class=

320

"pyoperator">.getdescription() <span class=

321

"pycomment"># shows column names and declared types

322

print x<span class=

323

"pyoperator">,y,z

342

324

343

###

325

###

344

326

### iterator - multiple statements

345

327

###

346

328

347

for m<font color=

348

"#0000C0">,n,o <font color=

349

"#C00000">in cursor.execute<font color=

350

"#0000C0">(<font color=

351

"#004080">"select x,y,z from foo ; select a,b,c from bar"<font color=

352

"#0000C0">):

353

print m<font color=

354

"#0000C0">,n,o

329

for m,<span class=

330

"pyname">n,o <span class=

331

"pykeyword">in cursor.<span class=

332

"pyname">execute(<span class=

333

"pystring">"select x,y,z from foo ; select a,b,c from bar"):

334

print m<span class=

335

"pyoperator">,n,o

355

336

356

###

337

###

357

338

### bindings - sequence

358

339

###

359

340

360

cursor.execute<font color=

361

"#0000C0">(<font color=

362

"#004080">"insert into foo values(?,?,?)"<font color=

363

"#0000C0">, (7<font color=

364

"#0000C0">, 'eight'<font color=

365

"#0000C0">, False))

366

cursor.execute<font color=

367

"#0000C0">(<font color=

368

"#004080">"insert into foo values(?,?,?1)"<font color=

369

"#0000C0">, ('one'<font color=

370

"#0000C0">, 'two'<font color=

371

"#0000C0">)) # nb sqlite does the numbers from 1

341

cursor.<span class=

342

"pyname">execute(<span class=

343

"pystring">"insert into foo values(?,?,?)", (<span class=

344

"pynumber">7, 'eight'<span class=

345

"pyoperator">, False))

346

cursor.<span class=

347

"pyname">execute(<span class=

348

"pystring">"insert into foo values(?,?,?1)", (<span class=

349

"pystring">'one', 'two'<span class=

350

"pyoperator">)) # nb sqlite does the numbers from 1

372

351

373

352

###

374

353

### bindings - dictionary

375

354

###

376

355

377

cursor.execute<font color=

378

"#0000C0">(<font color=

379

"#004080">"insert into foo values(:alpha, :beta, :gamma)"<font color=

380

"#0000C0">, {'alpha'<font color=

381

"#0000C0">: 1<font color=

382

"#0000C0">, 'beta'<font color=

383

"#0000C0">: 2<font color=

384

"#0000C0">, 'gamma'<font color=

385

"#0000C0">: 'three'})

356

cursor.<span class=

357

"pyname">execute(<span class=

358

"pystring">"insert into foo values(:alpha, :beta, :gamma)", {<span class=

359

"pystring">'alpha': 1<span class=

360

"pyoperator">, 'beta': <span class=

361

"pynumber">2, 'gamma'<span class=

362

"pyoperator">: 'three'})

386

363

387

###

364

###

388

365

### <a name="example-exectrace" id="example-exectrace">tracing execution</a>

389

366

###

390

367

391

def mytrace<font color=

392

"#0000C0">(statement, bindings<font color=

393

"#0000C0">):

394

"Called just before executing each statement"

395

print <font color=

396

"#004080">"SQL:",statement

397

if bindings:

398

print <font color=

399

"#004080">"Bindings:",bindings

400

return True <font color=

401

"#008000"># if you return False then execution is aborted

402

403

cursor.setexectrace<font color=

404

"#0000C0">(mytrace)

405

cursor.execute<font color=

406

"#0000C0">(<font color=

407

"#004080">"drop table bar ; create table bar(x,y,z); select * from foo where x=?", (3,))

408

409

SQL: drop table bar ;

368

def mytrace<span class=

369

"pyoperator">(statement, <span class=

370

"pyname">bindings):

371

"Called just before executing each statement"

372

print "SQL:"<span class=

373

"pyoperator">,statement

374

if bindings:

375

print "Bindings:"<span class=

376

"pyoperator">,bindings

377

return True <span class=

378

"pycomment"># if you return False then execution is aborted

379

380

cursor.<span class=

381

"pyname">setexectrace(mytrace<span class=

382

"pyoperator">)

383

cursor.<span class=

384

"pyname">execute(<span class=

385

"pystring">"drop table bar ; create table bar(x,y,z); select * from foo where x=?"<span class=

386

"pyoperator">, (3,))

387

388

SQL: drop table bar ;

410

389

SQL: create table bar(x,y,z);

411

390

SQL: select * from foo where x=?

412

391

Bindings: (3,)

413

414

###

392

393

###

415

394

### <a name="example-rowtrace" id="example-rowtrace">tracing results</a>

416

395

###

417

396

418

def rowtrace<font color=

419

"#0000C0">(*results):

420

<font color=

421

"#004080">"""Called with each row of results before they are handed off. You can return None to

397

def rowtrace<span class=

398

"pyoperator">(*results):

399

<span class=

400

"pystring">"""Called with each row of results before they are handed off. You can return None to

422

401

cause the row to be skipped or a different set of values to return"""

423

print <font color=

424

"#004080">"Row:",results

425

return results

426

427

cursor.setrowtrace<font color=

428

"#0000C0">(rowtrace)

429

for row <font color=

430

"#C00000">in cursor.execute<font color=

431

"#0000C0">(<font color=

432

"#004080">"select x,y from foo where x>3"):

433

pass

434

435

SQL: select x,y from foo where x>3

436

Row: (4, u'five')

437

Row: (7, u'eight')

402

print "Row:"<span class=

403

"pyoperator">,results

404

return results

405

406

cursor.setrowtrace<span class=

407

"pyoperator">(rowtrace)

408

for row <span class=

409

"pykeyword">in cursor.<span class=

410

"pyname">execute(<span class=

411

"pystring">"select x,y from foo where x>3"):

412

pass

413

414

SQL: select x,y from foo where x>3

415

Row: (4L, u'five')

416

Row: (7L, u'eight')

438

417

Row: (u'one', u'two')

439

440

# Clear tracers

441

cursor.setrowtrace<font color=

442

"#0000C0">(None)

443

cursor.setexectrace<font color=

444

"#0000C0">(None)

418

419

# Clear tracers

420

cursor.<span class=

421

"pyname">setrowtrace(None)

422

cursor.<span class=

423

"pyname">setexectrace(None)

445

424

446

###

425

###

447

426

### executemany

448

427

###

449

428

450

429

# (This will work correctly with multiple statements, as well as statements that

451

430

# return data. The second argument can be anything that is iterable.)

452

cursor.executemany<font color=

453

"#0000C0">(<font color=

454

"#004080">"insert into foo (x) values(?)"<font color=

455

"#0000C0">, ( [1<font color=

456

"#0000C0">], [2<font color=

457

"#0000C0">], [3] ) )

458

459

<font color=

460

"#008000"># You can also use it for statements that return data

461

for row <font color=

462

"#C00000">in cursor.executemany<font color=

463

"#0000C0">(<font color=

464

"#004080">"select * from foo where x=?"<font color=

465

"#0000C0">, ( [1<font color=

466

"#0000C0">], [2<font color=

467

"#0000C0">], [3] ) ):

468

print row

469

470

###

431

cursor.<span class=

432

"pyname">executemany(<span class=

433

"pystring">"insert into foo (x) values(?)", ( [<span class=

434

"pynumber">1], [2<span class=

435

"pyoperator">], [3] ) )

436

437

# You can also use it for statements that return data

438

for row <span class=

439

"pykeyword">in cursor.<span class=

440

"pyname">executemany(<span class=

441

"pystring">"select * from foo where x=?", ( [<span class=

442

"pynumber">1], [2<span class=

443

"pyoperator">], [3] ) ):

444

print row

445

446

###

471

447

### defining your own functions

472

448

###

473

449

474

def ilove7<font color=

475

"#0000C0">(*args):

476

"a scalar function"

477

print <font color=

478

"#004080">"ilove7 got",args<font color=

479

"#0000C0">,"but I love 7"

480

return 7

481

482

connection.createscalarfunction<font color=

483

"#0000C0">("seven"<font color=

484

"#0000C0">, ilove7)

485

486

for row <font color=

487

"#C00000">in cursor.execute<font color=

488

"#0000C0">(<font color=

489

"#004080">"select seven(x,y) from foo"):

490

print row

491

492

###

450

def ilove7<span class=

451

"pyoperator">(*args):

452

"a scalar function"

453

print "ilove7 got"<span class=

454

"pyoperator">,args,<span class=

455

"pystring">"but I love 7"

456

return 7

457

458

connection.<span class=

459

"pyname">createscalarfunction("seven"<span class=

460

"pyoperator">, ilove7)

461

462

for row <span class=

463

"pykeyword">in cursor.<span class=

464

"pyname">execute(<span class=

465

"pystring">"select seven(x,y) from foo"):

466

print row

467

468

###

493

469

### aggregate functions are more complex

494

470

###

495

471

496

472

# here we return the longest item when represented as a string

497

473

498

def longeststep<font color=

499

"#0000C0">(context, *args<font color=

500

"#0000C0">):

501

<font color=

502

"#004080">"are any of the arguments longer than our current candidate"

503

for arg <font color=

504

"#C00000">in args:

505

if len<font color=

506

"#0000C0">( str(arg<font color=

507

"#0000C0">) ) > len( context<font color=

508

"#0000C0">['longest'<font color=

509

"#0000C0">] ):

510

context[<font color=

511

"#004080">'longest']=str<font color=

512

"#0000C0">(arg)

513

514

def longestfinal<font color=

515

"#0000C0">(context):

516

"return the winner"

517

return context<font color=

518

"#0000C0">['longest']

519

520

def longestfactory():

521

<font color=

522

"#004080">"""called for a new query. The first item returned can be

474

def longeststep<span class=

475

"pyoperator">(context, *<span class=

476

"pyname">args):

477

"are any of the arguments longer than our current candidate"

478

for arg <span class=

479

"pykeyword">in args:

480

if len<span class=

481

"pyoperator">( str(<span class=

482

"pyname">arg) ) > len<span class=

483

"pyoperator">( context[<span class=

484

"pystring">'longest'] ):

485

context[<span class=

486

"pystring">'longest']=str<span class=

487

"pyoperator">(arg)

488

489

def longestfinal<span class=

490

"pyoperator">(context):

491

"return the winner"

492

return context<span class=

493

"pyoperator">['longest']

494

495

def longestfactory():

496

"""called for a new query. The first item returned can be

523

497

anything and is passed as the context to the step

524

498

and final methods. We use a dict."""

525

return <font color=

526

"#0000C0">( { 'longest'<font color=

527

"#0000C0">: '' <font color=

528

"#0000C0">}, longeststep<font color=

529

"#0000C0">, longestfinal)

530

531

connection<font color=

532

"#0000C0">.createaggregatefunction<font color=

533

"#0000C0">("longest"<font color=

534

"#0000C0">, longestfactory)

535

536

for row <font color=

537

"#C00000">in cursor.execute<font color=

538

"#0000C0">(<font color=

539

"#004080">"select longest(x) from foo"):

540

print row

541

542

###

499

return ( { <span class=

500

"pystring">'longest': '' <span class=

501

"pyoperator">}, longeststep, <span class=

502

"pyname">longestfinal)

503

504

connection.<span class=

505

"pyname">createaggregatefunction(<span class=

506

"pystring">"longest", longestfactory<span class=

507

"pyoperator">)

508

509

for row <span class=

510

"pykeyword">in cursor.<span class=

511

"pyname">execute(<span class=

512

"pystring">"select longest(x) from foo"):

513

print row

514

515

###

543

516

### Defining collations.

544

517

###

545

518

546

519

# The default sorting mechanisms don't understand numbers at the end of strings

547

520

# so here we define a collation that does

548

521

549

cursor.execute<font color=

550

"#0000C0">(<font color=

551

"#004080">"create table s(str)")

552

cursor.executemany<font color=

553

"#0000C0">(<font color=

554

"#004080">"insert into s values(?)",

555

( ["file1"<font color=

556

"#0000C0">], ["file7"<font color=

557

"#0000C0">], ["file17"<font color=

558

"#0000C0">], ["file20"<font color=

559

"#0000C0">], ["file3"<font color=

560

"#0000C0">] ) )

561

562

for row <font color=

563

"#C00000">in cursor.execute<font color=

564

"#0000C0">(<font color=

565

"#004080">"select * from s order by str"):

566

print row

567

568

(u'file1',)

522

cursor.<span class=

523

"pyname">execute("create table s(str)"<span class=

524

"pyoperator">)

525

cursor.<span class=

526

"pyname">executemany(<span class=

527

"pystring">"insert into s values(?)",

528

( ["file1"], [<span class=

529

"pystring">"file7"], ["file17"<span class=

530

"pyoperator">], ["file20"], [<span class=

531

"pystring">"file3"] ) )

532

533

for row <span class=

534

"pykeyword">in cursor.<span class=

535

"pyname">execute(<span class=

536

"pystring">"select * from s order by str"):

537

print row

538

539

(u'file1',)

569

540

(u'file17',)

570

541

(u'file20',)

571

542

(u'file3',)

572

543

(u'file7',)

573

574

def strnumcollate<font color=

575

"#0000C0">(s1, s2):

576

# return -1 if s1<s2, +1 if s1>s2 else 0

544

545

def strnumcollate<span class=

546

"pyoperator">(s1, <span class=

547

"pyname">s2):

548

# return -1 if s1<s2, +1 if s1>s2 else 0

577

549

578

550

# split values into two parts - the head and the numeric tail

579

values=[s1<font color=

580

"#0000C0">, s2]

581

for vn<font color=

582

"#0000C0">,v in enumerate<font color=

583

"#0000C0">(values):

584

for i <font color=

585

"#C00000">in range(len<font color=

586

"#0000C0">(v), <font color=

587

"#0080C0">0, -<font color=

588

"#0080C0">1):

589

if v<font color=

590

"#0000C0">[i-<font color=

591

"#0080C0">1] <font color=

592

"#C00000">not in "01234567890"<font color=

593

"#0000C0">:

594

break

595

try:

596

v=( v<font color=

597

"#0000C0">[:i], int<font color=

598

"#0000C0">(v[i:]) )

599

except ValueError<font color=

600

"#0000C0">:

601

v=( v<font color=

602

"#0000C0">[:i], None <font color=

603

"#0000C0">)

604

values[vn<font color=

605

"#0000C0">]=v

606

# compare

607

if values<font color=

608

"#0000C0">[0<font color=

609

"#0000C0">]<values[<font color=

610

"#0080C0">1]:

611

return <font color=

612

"#0000C0">-1

613

if values<font color=

614

"#0000C0">[0<font color=

615

"#0000C0">]>values[<font color=

616

"#0080C0">1]:

617

return 1

618

return 0

619

620

connection.createcollation<font color=

621

"#0000C0">("strnum"<font color=

622

"#0000C0">, strnumcollate)

623

624

for row <font color=

625

"#C00000">in cursor.execute<font color=

626

"#0000C0">(<font color=

627

"#004080">"select * from s order by str collate strnum"<font color=

628

"#0000C0">):

629

print row

630

631

(u'file1',)

551

values=[<span class=

552

"pyname">s1, s2]

553

for vn<span class=

554

"pyoperator">,v in <span class=

555

"pyname">enumerate(values):

556

for i <span class=

557

"pykeyword">in range(<span class=

558

"pyname">len(v<span class=

559

"pyoperator">), 0, -<span class=

560

"pynumber">1):

561

if v<span class=

562

"pyoperator">[i-<span class=

563

"pynumber">1] not in <span class=

564

"pystring">"01234567890":

565

break

566

try:

567

v=( <span class=

568

"pyname">v[:i<span class=

569

"pyoperator">], int(<span class=

570

"pyname">v[i:]) )

571

except ValueError:

572

v=( <span class=

573

"pyname">v[:i<span class=

574

"pyoperator">], None )

575

values[<span class=

576

"pyname">vn]=v

577

# compare

578

if values<span class=

579

"pyoperator">[0]<<span class=

580

"pyname">values[1]:

581

return -1

582

if values<span class=

583

"pyoperator">[0]><span class=

584

"pyname">values[1]:

585

return 1

586

return 0

587

588

connection.<span class=

589

"pyname">createcollation("strnum"<span class=

590

"pyoperator">, strnumcollate)

591

592

for row <span class=

593

"pykeyword">in cursor.<span class=

594

"pyname">execute(<span class=

595

"pystring">"select * from s order by str collate strnum"):

596

print row

597

598

(u'file1',)

632

599

(u'file3',)

633

600

(u'file7',)

634

601

(u'file17',)

635

602

(u'file20',)

636

637

###

603

604

###

638

605

### Authorizer (eg if you want to control what user supplied SQL can do)

639

606

###

640

607

641

def authorizer<font color=

642

"#0000C0">(operation, paramone<font color=

643

"#0000C0">, paramtwo<font color=

644

"#0000C0">, databasename<font color=

645

"#0000C0">, triggerorview):

646

<font color=

647

"#004080">"""Called when each operation is prepared. We can return SQLITE_OK, SQLITE_DENY or

608

def authorizer<span class=

609

"pyoperator">(operation, <span class=

610

"pyname">paramone, paramtwo<span class=

611

"pyoperator">, databasename, <span class=

612

"pyname">triggerorview):

613

"""Called when each operation is prepared. We can return SQLITE_OK, SQLITE_DENY or

648

614

SQLITE_IGNORE"""

649

# find the operation name

650

print apsw<font color=

651

"#0000C0">.mapping_authorizer_function<font color=

652

"#0000C0">[operation],

653

print paramone<font color=

654

"#0000C0">, paramtwo<font color=

655

"#0000C0">, databasename, triggerorview

656

if operation<font color=

657

"#0000C0">==apsw<font color=

658

"#0000C0">.SQLITE_CREATE_TABLE <font color=

659

"#C00000">and paramone<font color=

660

"#0000C0">.startswith(<font color=

661

"#004080">"private"):

662

return apsw<font color=

663

"#0000C0">.SQLITE_DENY <font color=

664

"#008000"># not allowed to create tables whose names start with private

665

666

return apsw<font color=

667

"#0000C0">.SQLITE_OK # always allow

668

669

connection.setauthorizer<font color=

670

"#0000C0">(authorizer)

671

cursor.execute<font color=

672

"#0000C0">(<font color=

673

"#004080">"insert into s values('foo')")

674

cursor.execute<font color=

675

"#0000C0">(<font color=

676

"#004080">"select str from s limit 1")

677

678

SQLITE_INSERT s None main None

615

# find the operation name

616

print apsw<span class=

617

"pyoperator">.mapping_authorizer_function<span class=

618

"pyoperator">[operation],

619

print paramone<span class=

620

"pyoperator">, paramtwo, <span class=

621

"pyname">databasename, triggerorview

622

if operation<span class=

623

"pyoperator">==apsw.<span class=

624

"pyname">SQLITE_CREATE_TABLE and paramone<span class=

625

"pyoperator">.startswith(<span class=

626

"pystring">"private"):

627

return apsw<span class=

628

"pyoperator">.SQLITE_DENY <span class=

629

"pycomment"># not allowed to create tables whose names start with private

630

631

return apsw<span class=

632

"pyoperator">.SQLITE_OK # always allow

633

634

connection.<span class=

635

"pyname">setauthorizer(authorizer<span class=

636

"pyoperator">)

637

cursor.<span class=

638

"pyname">execute(<span class=

639

"pystring">"insert into s values('foo')")

640

cursor.<span class=

641

"pyname">execute(<span class=

642

"pystring">"select str from s limit 1")

643

644

SQLITE_INSERT s None main None

679

645

SQLITE_SELECT None None None None

680

646

SQLITE_READ s str main None

681

682

# Cancel authorizer

683

connection.setauthorizer<font color=

684

"#0000C0">(None)

647

648

# Cancel authorizer

649

connection.<span class=

650

"pyname">setauthorizer(None<span class=

651

"pyoperator">)

685

652

686

###

653

###

687

654

### progress handler (SQLite 3 experimental feature)

688

655

###

689

656

690

657

# something to give us large numbers of random numbers

691

import random

692

def randomintegers<font color=

693

"#0000C0">(howmany):

694

for i <font color=

695

"#C00000">in xrange(howmany<font color=

696

"#0000C0">):

697

yield <font color=

698

"#0000C0">(random.randint<font color=

699

"#0000C0">(0<font color=

700

"#0000C0">,9999999999<font color=

701

"#0000C0">),)

702

703

# create a table with 500 random numbers

704

cursor.execute<font color=

705

"#0000C0">(<font color=

706

"#004080">"begin ; create table bigone(x)")

707

cursor.executemany<font color=

708

"#0000C0">(<font color=

709

"#004080">"insert into bigone values(?)"<font color=

710

"#0000C0">, randomintegers(<font color=

711

"#0080C0">500))

712

cursor.execute<font color=

713

"#0000C0">("commit")

714

715

# display an ascii spinner

716

_phcount=0

717

_phspinner="|/-\\"

718

def progresshandler<font color=

719

"#0000C0">():

720

global _phcount

721

sys.stdout<font color=

722

"#0000C0">.write(_phspinner<font color=

723

"#0000C0">[_phcount%len<font color=

724

"#0000C0">(_phspinner)]+chr<font color=

725

"#0000C0">(8<font color=

726

"#0000C0">)) # chr(8) is backspace

727

sys.stdout<font color=

728

"#0000C0">.flush()

729

_phcount+=1

730

time.sleep<font color=

731

"#0000C0">(0.1<font color=

732

"#0000C0">) <font color=

733

"#008000"># deliberate delay so we can see the spinner (SQLite is too fast otherwise!)

734

return <font color=

735

"#0080C0">0 # returning non-zero aborts

658

import random

659

def randomintegers<span class=

660

"pyoperator">(howmany):

661

for i <span class=

662

"pykeyword">in xrange(<span class=

663

"pyname">howmany):

664

yield (<span class=

665

"pyname">random.randint<span class=

666

"pyoperator">(0,<span class=

667

"pynumber">9999999999),)

668

669

# create a table with 100 random numbers

670

cursor.<span class=

671

"pyname">execute(<span class=

672

"pystring">"begin ; create table bigone(x)")

673

cursor.<span class=

674

"pyname">executemany(<span class=

675

"pystring">"insert into bigone values(?)", <span class=

676

"pyname">randomintegers(100<span class=

677

"pyoperator">))

678

cursor.<span class=

679

"pyname">execute("commit"<span class=

680

"pyoperator">)

681

682

# display an ascii spinner

683

_phcount=0

684

_phspinner="|/-\\"

685

def progresshandler():

686

global _phcount

687

sys.stdout<span class=

688

"pyoperator">.write(<span class=

689

"pyname">_phspinner[_phcount<span class=

690

"pyoperator">%len(<span class=

691

"pyname">_phspinner)]+chr<span class=

692

"pyoperator">(8)) <span class=

693

"pycomment"># chr(8) is backspace

694

sys.<span class=

695

"pyname">stdout.flush()

696

_phcount+=1

697

time.<span class=

698

"pyname">sleep(0.1<span class=

699

"pyoperator">) <span class=

700

"pycomment"># deliberate delay so we can see the spinner (SQLite is too fast otherwise!)

701

return 0 <span class=

702

"pycomment"># returning non-zero aborts

736

703

737

704

# register progresshandler every 20 instructions

738

connection.setprogresshandler<font color=

739

"#0000C0">(progresshandler, <font color=

740

"#0080C0">20)

741

742

<font color=

743

"#008000"># see it in action - sorting 500 numbers to find the biggest takes a while

744

print <font color=

745

"#004080">"spinny thing -> ",

746

for i <font color=

747

"#C00000">in cursor.execute<font color=

748

"#0000C0">(<font color=

749

"#004080">"select max(x) from bigone"):

750

print # newline

751

print i <font color=

752

"#008000"># and the maximum number

753

754

connection.setprogresshandler<font color=

755

"#0000C0">(None)

756

757

###

705

connection.<span class=

706

"pyname">setprogresshandler(<span class=

707

"pyname">progresshandler, 20<span class=

708

"pyoperator">)

709

710

# see it in action - sorting 100 numbers to find the biggest takes a while

711

print "spinny thing -> "<span class=

712

"pyoperator">,

713

for i in <span class=

714

"pyname">cursor.execute<span class=

715

"pyoperator">("select max(x) from bigone"):

716

print # newline

717

print i <span class=

718

"pycomment"># and the maximum number

719

720

connection.<span class=

721

"pyname">setprogresshandler(None<span class=

722

"pyoperator">)

723

724

###

758

725

### commit hook (SQLite3 experimental feature)

759

726

###

760

727

761

def mycommithook():

762

print <font color=

763

"#004080">"in commit hook"

764

hour=time<font color=

765

"#0000C0">.localtime()[<font color=

766

"#0080C0">3]

767

if hour<font color=

768

"#0000C0"><8 <font color=

769

"#C00000">or hour><font color=

770

"#0080C0">17:

771

print <font color=

772

"#004080">"no commits out of hours"

773

return <font color=

774

"#0080C0">1 <font color=

775

"#008000"># abort commits outside of 8am through 6pm

776

print <font color=

777

"#004080">"commits okay at this time"

778

return <font color=

779

"#0080C0">0 # let commit go ahead

780

781

connection.setcommithook<font color=

782

"#0000C0">(mycommithook)

783

try:

784

cursor.execute<font color=

785

"#0000C0">(<font color=

786

"#004080">"begin; create table example(x,y,z); insert into example values (3,4,5) ; commit")

787

except apsw<font color=

788

"#0000C0">.ConstraintError:

789

print <font color=

790

"#004080">"commit was not allowed"

791

792

connection.setcommithook<font color=

793

"#0000C0">(None)

794

795

###

728

def mycommithook():

729

print "in commit hook"

730

hour=<span class=

731

"pyname">time.localtime<span class=

732

"pyoperator">()[3]

733

if hour<span class=

734

"pyoperator"><8 or <span class=

735

"pyname">hour>17:

736

print "no commits out of hours"

737

return 1 <span class=

738

"pycomment"># abort commits outside of 8am through 6pm

739

print "commits okay at this time"

740

return 0 <span class=

741

"pycomment"># let commit go ahead

742

743

connection.<span class=

744

"pyname">setcommithook(mycommithook<span class=

745

"pyoperator">)

746

try:

747

cursor.<span class=

748

"pyname">execute(<span class=

749

"pystring">"begin; create table example(x,y,z); insert into example values (3,4,5) ; commit"<span class=

750

"pyoperator">)

751

except apsw<span class=

752

"pyoperator">.ConstraintError:

753

print "commit was not allowed"

754

755

connection.<span class=

756

"pyname">setcommithook(None<span class=

757

"pyoperator">)

758

759

###

760

### <a name="example-blobio" id="example-blobio">Blob I/O</a>

761

###

762

763

cursor.<span class=

764

"pyname">execute(<span class=

765

"pystring">"create table blobby(x,y)")

766

# Add a blob we will fill in later

767

cursor.<span class=

768

"pyname">execute(<span class=

769

"pystring">"insert into blobby values(1,zeroblob(10000))")

770

# Or as a binding

771

cursor.<span class=

772

"pyname">execute(<span class=

773

"pystring">"insert into blobby values(2,?)", (<span class=

774

"pyname">apsw.zeroblob<span class=

775

"pyoperator">(20000),))

776

# Open a blob for writing. We need to know the rowid

777

rowid=cursor<span class=

778

"pyoperator">.execute(<span class=

779

"pystring">"select ROWID from blobby where x=1").<span class=

780

"pyname">next()[0]

781

blob=<span class=

782

"pyname">connection.blobopen<span class=

783

"pyoperator">("main", <span class=

784

"pystring">"blobby", "y"<span class=

785

"pyoperator">, rowid, <span class=

786

"pynumber">1) # 1 is for read/write

787

blob.write<span class=

788

"pyoperator">("hello world")

789

blob.seek<span class=

790

"pyoperator">(2000)

791

blob.write<span class=

792

"pyoperator">("hello world, again")

793

blob.close<span class=

794

"pyoperator">()

795

796

###

796

797

### Virtual tables

797

798

###

798

799

800

# This virtual table stores information about files in a set of

800

801

# directories so you can execute SQL queries

801

802

def getfiledata<font color=

803

"#0000C0">(directories):

804

columns=None

805

data=[]

806

counter=1

807

for directory <font color=

808

"#C00000">in directories:

809

for f <font color=

810

"#C00000">in os.listdir<font color=

811

"#0000C0">(directory):

812

if not os<font color=

813

"#0000C0">.path.isfile<font color=

814

"#0000C0">(os.path<font color=

815

"#0000C0">.join(directory<font color=

816

"#0000C0">,f)):

817

continue

818

counter+=<font color=

819

"#0080C0">1

820

st=os<font color=

821

"#0000C0">.stat(os<font color=

822

"#0000C0">.path.join<font color=

823

"#0000C0">(directory,f<font color=

824

"#0000C0">))

825

if columns <font color=

826

"#C00000">is None:

827

columns=[<font color=

828

"#004080">"rowid", <font color=

829

"#004080">"name", <font color=

830

"#004080">"directory"]+[x <font color=

831

"#C00000">for x in dir<font color=

832

"#0000C0">(st) <font color=

833

"#C00000">if x.startswith<font color=

834

"#0000C0">("st_")]

835

data.append<font color=

836

"#0000C0">( [counter, f<font color=

837

"#0000C0">, directory<font color=

838

"#0000C0">] + [getattr(st<font color=

839

"#0000C0">,x) <font color=

840

"#C00000">for x in columns<font color=

841

"#0000C0">[3:]] )

842

return columns<font color=

843

"#0000C0">, data

844

845

# This gets registered with the Connection

846

class Source:

847

def Create<font color=

848

"#0000C0">(self, db<font color=

849

"#0000C0">, modulename, dbname<font color=

850

"#0000C0">, tablename, *args<font color=

851

"#0000C0">):

852

columns,data<font color=

853

"#0000C0">=getfiledata([eval<font color=

854

"#0000C0">(a) <font color=

855

"#C00000">for a in args<font color=

856

"#0000C0">]) # eval strips off layer of quotes

857

schema=<font color=

858

"#004080">"create table foo("+<font color=

859

"#004080">','.join<font color=

860

"#0000C0">(["'%s'" <font color=

861

"#0000C0">% (x,) <font color=

862

"#C00000">for x in columns<font color=

863

"#0000C0">[1<font color=

864

"#0000C0">:]])+")"

865

return schema<font color=

866

"#0000C0">,Table(columns<font color=

867

"#0000C0">,data)

868

Connect=Create

869

870

# Represents a table

871

class Table:

872

def __init__<font color=

873

"#0000C0">(self, columns<font color=

874

"#0000C0">, data):

875

self.columns<font color=

876

"#0000C0">=columns

877

self.data<font color=

878

"#0000C0">=data

879

880

def BestIndex<font color=

881

"#0000C0">(self, *args<font color=

882

"#0000C0">):

883

return None

884

885

def Open<font color=

886

"#0000C0">(self):

887

return Cursor<font color=

888

"#0000C0">(self)

889

890

def Disconnect<font color=

891

"#0000C0">(self):

892

pass

893

894

Destroy=Disconnect

895

896

# Represents a cursor

897

class Cursor:

898

def __init__<font color=

899

"#0000C0">(self, table<font color=

900

"#0000C0">):

901

self.table<font color=

902

"#0000C0">=table

903

904

def Filter<font color=

905

"#0000C0">(self, *args<font color=

906

"#0000C0">):

907

self.pos<font color=

908

"#0000C0">=0

909

910

def Eof<font color=

911

"#0000C0">(self):

912

return self<font color=

913

"#0000C0">.pos>=len<font color=

914

"#0000C0">(self.table<font color=

915

"#0000C0">.data)

916

917

def Rowid<font color=

918

"#0000C0">(self):

919

return self<font color=

920

"#0000C0">.table.data<font color=

921

"#0000C0">[self.pos<font color=

922

"#0000C0">][0]

923

924

def Column<font color=

925

"#0000C0">(self, col<font color=

926

"#0000C0">):

927

return self<font color=

928

"#0000C0">.table.data<font color=

929

"#0000C0">[self.pos<font color=

930

"#0000C0">][1<font color=

931

"#0000C0">+col]

932

933

def Next<font color=

934

"#0000C0">(self):

935

self.pos<font color=

936

"#0000C0">+=1

937

938

def Close<font color=

939

"#0000C0">(self):

940

pass

941

942

# Register the module as filesource

943

connection.createmodule<font color=

944

"#0000C0">("filesource"<font color=

945

"#0000C0">, Source())

946

947

<font color=

948

"#008000"># Arguments to module - all directories in sys.path

949

sysdirs=<font color=

950

"#004080">",".join<font color=

951

"#0000C0">(["'%s'" <font color=

952

"#0000C0">% (x,) <font color=

953

"#C00000">for x in sys<font color=

954

"#0000C0">.path[<font color=

955

"#0080C0">1:] <font color=

956

"#C00000">if len(x<font color=

957

"#0000C0">) and os<font color=

958

"#0000C0">.path.isdir<font color=

959

"#0000C0">(x)])

960

cursor.execute<font color=

961

"#0000C0">(<font color=

962

"#004080">"create virtual table sysfiles using filesource("<font color=

963

"#0000C0">+sysdirs+<font color=

964

"#004080">")")

965

966

# Which 3 files are the biggest?

967

for size<font color=

968

"#0000C0">,directory,file <font color=

969

"#C00000">in cursor.execute<font color=

970

"#0000C0">(<font color=

971

"#004080">"select st_size,directory,name from sysfiles order by st_size desc limit 3"):

972

print size<font color=

973

"#0000C0">,file,directory

974

975

1610924 apsw.so /usr/lib64/python2.4/site-packages

976

1527536 _lcms.so /usr/lib64/python2.4/site-packages

977

736616 lapack_lite.so /usr/lib64/python2.4/site-packages/Numeric

978

979

# Which 3 files are the oldest?

980

for ctime<font color=

981

"#0000C0">,directory,file <font color=

982

"#C00000">in cursor.execute<font color=

983

"#0000C0">(<font color=

984

"#004080">"select st_ctime,directory,name from sysfiles order by st_ctime limit 3"):

985

print ctime<font color=

986

"#0000C0">,file,directory

987

988

1157247798 libcdemu.py /usr/lib64/python2.4/site-packages

989

1157247798 libcdemu.pyo /usr/lib64/python2.4/site-packages

990

1157247798 libcdemu.pyc /usr/lib64/python2.4/site-packages

991

992

###

803

def getfiledata<span class=

804

"pyoperator">(directories):

805

columns=None

806

data=[]

807

counter=1

808

for directory <span class=

809

"pykeyword">in directories:

810

for f <span class=

811

"pykeyword">in os.<span class=

812

"pyname">listdir(directory<span class=

813

"pyoperator">):

814

if not os<span class=

815

"pyoperator">.path.<span class=

816

"pyname">isfile(os<span class=

817

"pyoperator">.path.<span class=

818

"pyname">join(directory<span class=

819

"pyoperator">,f)):

820

continue

821

counter+=1

822

st=<span class=

823

"pyname">os.stat<span class=

824

"pyoperator">(os.<span class=

825

"pyname">path.join<span class=

826

"pyoperator">(directory,<span class=

827

"pyname">f))

828

if columns <span class=

829

"pykeyword">is None:

830

columns=[<span class=

831

"pystring">"rowid", "name"<span class=

832

"pyoperator">, "directory"]+[<span class=

833

"pyname">x for x <span class=

834

"pykeyword">in dir(<span class=

835

"pyname">st) if <span class=

836

"pyname">x.startswith<span class=

837

"pyoperator">("st_")]

838

data.<span class=

839

"pyname">append( [counter<span class=

840

"pyoperator">, f, <span class=

841

"pyname">directory] + [getattr<span class=

842

"pyoperator">(st,<span class=

843

"pyname">x) for <span class=

844

"pyname">x in columns<span class=

845

"pyoperator">[3:]] )

846

return columns<span class=

847

"pyoperator">, data

848

849

# This gets registered with the Connection

850

class Source:

851

def Create<span class=

852

"pyoperator">(self, <span class=

853

"pyname">db, modulename<span class=

854

"pyoperator">, dbname, <span class=

855

"pyname">tablename, *args):

856

columns,<span class=

857

"pyname">data=getfiledata<span class=

858

"pyoperator">([eval(<span class=

859

"pyname">a.replace<span class=

860

"pyoperator">("\\", <span class=

861

"pystring">"\\\\")) for <span class=

862

"pyname">a in args<span class=

863

"pyoperator">]) # eval strips off layer of quotes

864

schema=<span class=

865

"pystring">"create table foo("+','<span class=

866

"pyoperator">.join([<span class=

867

"pystring">"'%s'" % (x<span class=

868

"pyoperator">,) for x <span class=

869

"pykeyword">in columns[<span class=

870

"pynumber">1:]])+")"

871

return schema<span class=

872

"pyoperator">,Table(<span class=

873

"pyname">columns,data)

874

Connect=Create

875

876

# Represents a table

877

class Table:

878

def __init__<span class=

879

"pyoperator">(self, <span class=

880

"pyname">columns, data):

881

self.<span class=

882

"pyname">columns=columns

883

self.data<span class=

884

"pyoperator">=data

885

886

def BestIndex<span class=

887

"pyoperator">(self, *<span class=

888

"pyname">args):

889

return None

890

891

def Open<span class=

892

"pyoperator">(self):

893

return Cursor<span class=

894

"pyoperator">(self)

895

896

def Disconnect<span class=

897

"pyoperator">(self):

898

pass

899

900

Destroy=Disconnect

901

902

# Represents a cursor

903

class Cursor:

904

def __init__<span class=

905

"pyoperator">(self, <span class=

906

"pyname">table):

907

self.<span class=

908

"pyname">table=table

909

910

def Filter<span class=

911

"pyoperator">(self, *<span class=

912

"pyname">args):

913

self.<span class=

914

"pyname">pos=0

915

916

def Eof<span class=

917

"pyoperator">(self):

918

return self<span class=

919

"pyoperator">.pos>=<span class=

920

"pyname">len(self<span class=

921

"pyoperator">.table.<span class=

922

"pyname">data)

923

924

def Rowid<span class=

925

"pyoperator">(self):

926

return self<span class=

927

"pyoperator">.table.<span class=

928

"pyname">data[self<span class=

929

"pyoperator">.pos][<span class=

930

"pynumber">0]

931

932

def Column<span class=

933

"pyoperator">(self, <span class=

934

"pyname">col):

935

return self<span class=

936

"pyoperator">.table.<span class=

937

"pyname">data[self<span class=

938

"pyoperator">.pos][<span class=

939

"pynumber">1+col]

940

941

def Next<span class=

942

"pyoperator">(self):

943

self.<span class=

944

"pyname">pos+=1

945

946

def Close<span class=

947

"pyoperator">(self):

948

pass

949

950

# Register the module as filesource

951

connection.<span class=

952

"pyname">createmodule("filesource"<span class=

953

"pyoperator">, Source())

954

955

# Arguments to module - all directories in sys.path

956

sysdirs=<span class=

957

"pystring">",".join<span class=

958

"pyoperator">(["'%s'" % (<span class=

959

"pyname">x,) for <span class=

960

"pyname">x in sys<span class=

961

"pyoperator">.path[<span class=

962

"pynumber">1:] if <span class=

963

"pyname">len(x<span class=

964

"pyoperator">) and os<span class=

965

"pyoperator">.path.<span class=

966

"pyname">isdir(x)])

967

cursor.<span class=

968

"pyname">execute(<span class=

969

"pystring">"create virtual table sysfiles using filesource("+<span class=

970

"pyname">sysdirs+")")

971

972

# Which 3 files are the biggest?

973

for size,<span class=

974

"pyname">directory,file <span class=

975

"pykeyword">in cursor.<span class=

976

"pyname">execute(<span class=

977

"pystring">"select st_size,directory,name from sysfiles order by st_size desc limit 3"<span class=

978

"pyoperator">):

979

print size<span class=

980

"pyoperator">,file,directory

981

982

8625144 qt.so /usr/lib/python2.5/site-packages

983

786208 qtsql.so /usr/lib/python2.5/site-packages

984

481008 unicodedata.so /usr/lib/python2.5/lib-dynload

985

986

# Which 3 files are the oldest?

987

for ctime<span class=

988

"pyoperator">,directory,<span class=

989

"pyname">file in cursor<span class=

990

"pyoperator">.execute(<span class=

991

"pystring">"select st_ctime,directory,name from sysfiles order by st_ctime limit 3"):

992

print ctime<span class=

993

"pyoperator">,file,directory

994

995

1194713443.0 GnuPGInterface-0.3.2.egg-info /var/lib/python-support/python2.5

996

1194713443.0 GnuPGInterface.py /var/lib/python-support/python2.5

997

1194713447.0 sexy.so /usr/lib/python2.5/site-packages/gtk-2.0

998

999

###

1000

### Limits

1001

###

1002

1003

# Print some limits

1004

for limit <span class=

1005

"pykeyword">in ("LENGTH"<span class=

1006

"pyoperator">, "COLUMN", <span class=

1007

"pystring">"ATTACHED"):

1008

name=<span class=

1009

"pystring">"SQLITE_LIMIT_"+limit

1010

maxname="SQLITE_MAX_"<span class=

1011

"pyoperator">+limit # compile time

1012

orig=<span class=

1013

"pyname">connection.limit<span class=

1014

"pyoperator">(getattr(<span class=

1015

"pyname">apsw, name))

1016

print name<span class=

1017

"pyoperator">, orig

1018

# To get the maximum, set to 0x7fffffff and then read value back

1019

connection.<span class=

1020

"pyname">limit(getattr<span class=

1021

"pyoperator">(apsw, <span class=

1022

"pyname">name), 0x7fffffff<span class=

1023

"pyoperator">)

1024

max=<span class=

1025

"pyname">connection.limit<span class=

1026

"pyoperator">(getattr(<span class=

1027

"pyname">apsw, name))

1028

print maxname<span class=

1029

"pyoperator">, max

1030

1031

# Set limit for size of a string

1032

cursor.<span class=

1033

"pyname">execute(<span class=

1034

"pystring">"create table testlimit(s)")

1035

cursor.<span class=

1036

"pyname">execute(<span class=

1037

"pystring">"insert into testlimit values(?)", ( <span class=

1038

"pystring">"x"*1024<span class=

1039

"pyoperator">, )) # 1024 char string

1040

connection.<span class=

1041

"pyname">limit(apsw<span class=

1042

"pyoperator">.SQLITE_LIMIT_LENGTH, <span class=

1043

"pynumber">1023) # limit is now 1023

1044

try:

1045

cursor.<span class=

1046

"pyname">execute(<span class=

1047

"pystring">"insert into testlimit values(?)", ( <span class=

1048

"pystring">"y"*1024, ))

1049

print "string exceeding limit was inserted"

1050

except apsw<span class=

1051

"pyoperator">.TooBigError:

1052

print "Caught toobig exception"

1053

1054

1055

SQLITE_LIMIT_LENGTH 1000000000

1056

SQLITE_MAX_LENGTH 1000000000

1057

SQLITE_LIMIT_COLUMN 2000

1058

SQLITE_MAX_COLUMN 2000

1059

SQLITE_LIMIT_ATTACHED 10

1060

SQLITE_MAX_ATTACHED 10

1061

Caught toobig exception

1062

1063

###

993

1064

### Cleanup

994

1065

###

995

1066

996

# We must close connections

997

connection.close<font color=

998

"#0000C0">(True) <font color=

999

"#008000"># force it since we want to exit

1067

# We can close connections manually (useful if you want to catch exceptions)

1068

# but you don't have to

1069

connection.<span class=

1070

"pyname">close(True<span class=

1071

"pyoperator">) # force it since we want to exit

1000

1072

1001

1073

</pre>

1002

1074

</blockquote>

1008

1080

1009

1081

<pre>

1010

1082

python setup.py install

1083

1011

1084

</pre>

1012

1085

</blockquote>

1013

1086

1014

On Windows the above command uses Visual C++. You can use MinGW with the

1015

command below. (If MinGW complains about missing Python functions starting

1016

with <code>_imp__Py_</code> then run <code>mingwsetup.bat</code> which will

1017

ensure your Python distribution is initialized for MinGW compilation).

1087

On Windows the above command uses Visual C++. You can use MinGW with the command below. (If MinGW complains about

1088

missing Python functions starting with <code>_imp__Py_</code> then run <code>mingwsetup.bat</code> which will ensure

1089

your Python distribution is initialized for MinGW compilation).

1018

1090

1019

1091

1020

1092

<pre>

1021

1093

python setup.py build --compile=mingw32 install

1094

1022

1095

</pre>

1023

1096

</blockquote>

1024

1097

1025

By default whatever SQLite 3 you already have on your system is used. If

1026

you place a copy of the headers and library in a <code>sqlite3</code>

1027

subdirectory then that will be used instead. It is highly recommended that

1028

you build SQLite into APSW. Here is a quick and easy way of doing everything

1029

on Linux/Mac or Windows with MinGW, including the SQLite library statically

1030

into the extension (ie no external DLLs/shared libraries will be needed at

1031

runtime). You should follow these instructions with your current directory

1032

being where you extracted the APSW source to.

1033

1034

1035

Download the <a href="http://www.sqlite.org/download.html">SQLite 3

1036

code</a>.

1037

1038

<dl>

1039

<dt>Windows

1040

Get the processed .zip</dt>

1041

1042

<dd>

1043

<pre>

1044

> mkdir sqlite3

1045

> cd sqlite3

1046

> unzip sqlite-source-3_3_10.zip

1047

> del tclsqlite.c

1048

> gcc -DTHREADSAFE -DNDEBUG -O3 -c *.c

1049

> ar r libsqlite3.a *.o

1050

> ranlib libsqlite3.a

1051

> cd ..

1052

> python setup.py build --compile=mingw32 install

1098

<h2><a name="Finding" id="Finding">Finding SQLite 3</a></h2>

1099

1100

SQLite 3 is needed during the build process. These methods are tried in order:

1101

1102

<dl>

1103

<dt><a href="http://www.sqlite.org/cvstrac/wiki?p=TheAmalgamation">Amalgamation</a></dt>

1104

1105

<dd>The file <tt>sqlite3.c</tt> and then <tt>sqlite3/sqlite3.c</tt> is looked for.</dd>

1106

1107

<dt>Local build</dt>

1108

1109

<dd>The header <tt>sqlite3/sqlite3.h</tt> and library <tt>sqlite3/libsqlite3.{a,so,dll}</tt> is looked for.</dd>

1110

1111

<dt>System</dt>

1112

1113

<dd>The default compiler include path (eg <tt>/usr/include</tt>) and library path (eg <tt>/usr/lib</tt>) are

1114

used.</dd>

1115

</dl>

1116

1117

Note that if you compiled SQLite with any OMIT flags (eg <tt>SQLITE_OMIT_LOAD_EXTENSION</tt>) then you should edit

1118

<tt>setup.py</tt> to add the same flags.

1119

1120

<h2><a name="Recommended" id="Recommended">Recommended</a></h2>

1121

1122

These instructions show how to build using the amalgamation. Any existing SQLite on your system is ignored at

1123

build time and runtime. (Note that you can even use APSW in the same process as a different SQLite is used by other

1124

libraries - this happens a lot on Mac.) You should follow these instructions with your current directory being where

1125

you extracted the APSW source to. You can use 3.5.9 or later versions of SQLite.

1126

1127

<dl>

1128

<dt>Windows</dt>

1129

</dl>

1130

<pre>

1131

Manually download and extract <a href=

1132

"http://sqlite.org/sqlite-amalgamation-3_5_9.zip">http://sqlite.org/sqlite-amalgamation-3_5_9.zip</a>

1133

> python setup.py build --compile=mingw32 install <font color=

1134

"blue">Leave out --compile=... flag if using Microsoft compiler

1053

1135

> python -c "import apsw ; print apsw.sqlitelibversion(), apsw.apswversion()"

1054

</pre>

1055

</dd>

1056

1057

<dt>Mac/Linux/etc

1058

Get the normal source.</dt>

1059

1060

<dd>

1061

<pre>

1062

$ wget http://www.sqlite.org/sqlite-3.3.13.tar.gz

1063

$ tar xvfz sqlite-3.3.13.tar.gz

1064

$ mv sqlite-3.3.13 sqlite3

1065

$ cd sqlite3

1066

<font color=

1067

"blue"># The static library is not built for inclusion into a seperate shared library

1068

# by default. If using gcc, then do this

1069

$ env CC="gcc -fPIC" CFLAGS="-DHAVE_DLOPEN" ./configure --enable-threadsafe --disable-tcl

1070

# otherwise do this

1071

$ env CFLAGS="-DHAVE_DLOPEN" ./configure --enable-threadsafe --disable-tcl

1072

<font color=

1073

"blue"># The <tt>CFLAGS="-DHAVE_DLOPEN</tt> bit is needed for loading dynamic

1074

# extensions. See SQLite bug <a href=

1075

"http://www.sqlite.org/cvstrac/tktview?tn=2082">2082</a>

1076

$ make

1077

$ cp .libs/*.a .

1078

$ cd ..

1136

> python test.py # optional - checks everything works correctly

1137

1138

</pre>Note that there will be many warnings during the compilation step about sqlite3.c, <a href=

1139

"http://sqlite.org/faq.html#q17">but they are harmless</a>.

1140

1141

<dl>

1142

<dt>Mac/Linux/etc</dt>

1143

</dl>

1144

<pre>

1145

$ wget <a href="http://sqlite.org/sqlite-amalgamation-3_5_9.zip">http://sqlite.org/sqlite-amalgamation-3_5_9.zip</a>

1146

$ unzip sqlite-amalgamation-3_5_9.zip

1079

1147

$ python setup.py install

1080

$ python -c "import apsw ; print apsw.sqlitelibversion(), apsw.apswversion()"

1081

</pre>

1082

</dd>

1083

</dl>

1084

</blockquote>

1085

1086

The extension just turns into a single file apsw.so (Linux/Mac) or

1087

apsw.pyd (Windows). You don't need to install it and can drop it into any

1088

directory that is more convenient for you and that your code can reach. To

1089

just do the build and not install, leave out <code>install</code> from the

1090

lines above and add <code>build</code> if it isn't already there.

1091

1092

If you want to check that your build is correct then you can run the unit

1093

tests. Run <tt>tests.py</tt>. It will print the APSW file used, APSW and

1094

SQLite versions and then run lots of tests all of which should pass.

1148

$ python -c "import apsw ; print apsw.sqlitelibversion(), apsw.apswversion()"

1149

$ python test.py # optional - checks everything works correctly

1150

1151

</pre>Note that there will be many warnings during the compilation step about sqlite3.c, <a href=

1152

"http://sqlite.org/faq.html#q17">but they are harmless</a>.

1153

1154

The extension just turns into a single file apsw.so (Linux/Mac) or apsw.pyd (Windows). You don't need to install

1155

it and can drop it into any directory that is more convenient for you and that your code can reach. To just do the

1156

build and not install, leave out <code>install</code> from the lines above and add <code>build</code> if it isn't

1157

already there.

1158

1159

If you want to check that your build is correct then you can run the unit tests. Run <tt>tests.py</tt>. It will

1160

print the APSW file used, APSW and SQLite versions and then run lots of tests all of which should pass.

1095

1161

1096

1162

<h1><a name="APIReference" id="APIReference">API Reference</a></h1>

1097

1163

1098

Everything you can do from the <a href=

1099

"http://www.sqlite.org/capi3ref.html">SQLite 3 C API</a> you can do from

1100

Python. The documentation below notes which C API functions are called where

1101

you can get further details on what happens. The only C function not

1102

implemented is <a href=

1103

"http://www.sqlite.org/capi3ref.html#sqlite3_collation_needed">sqlite3_collation_needed</a>.

1104

(You can still add collations, you just can't use this function to find out

1105

about them on-demand.) Additionally <a href=

1106

"http://www.sqlite.org/capi3ref.html#sqlite3_trace">sqlite3_trace</a> is not

1107

wrapped but instead <a href="#tracers">tracers</a> are provided that have

1108

more functionality.

1109

1110

Some functions are marked experimental in the SQLite API. These have also

1111

been made available, but as the SQLite documentation notes these functions

1112

may change form or disappear in future versions of SQLite. You can exclude

1113

these functions by commenting out the relevant line in the

1114

<code>setup.py</code> when building aspw.

1115

1116

Various methods create functions, collations and set various hooks and

1117

handlers. To remove the relevant function/collation/hook/handler, pass in

1118

None as the callable method.

1164

Everything you can do from the <a href="http://www.sqlite.org/c3ref/intro.html">SQLite 3 C API</a> you can do from

1165

Python. The documentation below notes which C API functions are called where you can get further details on what

1166

happens. The only functionality not implemented is <a href="http://www.sqlite.org/c3ref/vfs.html">VFS</a> because it

1167

is being changed for SQLite 3.6. Additionally <a href="http://sqlite.org/c3ref/profile.html">sqlite3_trace</a> is not

1168

wrapped but instead <a href="#tracers">tracers</a> are provided that have more functionality.

1169

1170

Some functions are marked experimental in the SQLite API. These have also been made available, but as the SQLite

1171

documentation notes these functions may change form or disappear in future versions of SQLite. You can exclude these

1172

functions by commenting out the relevant line in the <code>setup.py</code> when building aspw.

1173

1174

Various methods create functions, collations and set various hooks and handlers. To remove the relevant

1175

function/collation/hook/handler, pass in None as the callable method.

1119

1176

1120

1177

<h2><a name="module-methods" id="module-methods">Module methods</a></h2>

1121

1178

1123

1180

<dt>sqlitelibversion()</dt>

1124

1181

1125

1182

<dd>

1126

Returns the version of the SQLite library as a string. This function

1127

calls <a href=

1128

"http://www.sqlite.org/capi3ref.html#sqlite3_libversion">sqlite3_libversion</a>.

1183

Returns the version of the SQLite library as a string. This function calls <a href=

1184

"http://sqlite.org/c3ref/libversion.html">sqlite3_libversion</a>.

1129

1185

</dd>

1130

1186

1131

1187

<dt>apswversion()</dt>

1137

1193

<dt>enablesharedcache(boolean)</dt>

1138

1194

1139

1195

<dd>

1140

Calls <a href=

1141

"http://www.sqlite.org/capi3ref.html#sqlite3_enable_shared_cache">sqlite3_enable_shared_cache</a>.

1142

This sets the <a href="http://www.sqlite.org/sharedcache.html">shared

1143

cache mode</a> which was introduced in SQLite 3.3. Note that it only

1144

affects the current thread and should be called before any databases are

1145

opened. If called after that then you'll get MisuseError. APSW already

1146

enforces the other conditions required to use this functionality, namely

1147

that all operations on a Connection must happen in the same thread.

1148

Consequently you can safely use this functionality. You do not need to

1149

worry about <a href=

1150

"http://www.sqlite.org/capi3ref.html#sqlite3_thread_cleanup">sqlite3_thread_cleanup</a>

1151

as that is only needed when abnormally terminating a thread (something

1152

you can't do in Python). Python's normal reference counting ensures all

1153

objects are cleaned up, and SQLite automatically cleans up the shared

1154

cache when the last Connection in a thread is closed.

1196

Calls <a href="http://sqlite.org/c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache</a>. This sets

1197

the <a href="http://www.sqlite.org/sharedcache.html">shared cache mode</a> which was introduced in SQLite

1198

3.3.

1155

1199

</dd>

1156

1200

1157

1201

<dt>createmodule("modulename", module_object)</dt>

1158

1202

1159

1203

<dd>

1160

Registers a virtual table. See <a href="#VirtualTables">virtual

1161

tables</a> for more details.

1204

Registers a virtual table. See <a href="#VirtualTables">virtual tables</a> for more details.

1162

1205

</dd>

1163

1206

</dl>

1164

1207

1165

<h2><a name="Module_variables" id="Module_variables">Module

1166

variables</a></h2>

1208

<h2><a name="Module_variables" id="Module_variables">Module variables</a></h2>

1167

1209

1168

1210

<dl>

1169

1211

<dt>connection_hooks</dt>

1170

1212

1171

1213

<dd>

1172

The purpose of the hooks is to allow the easy registration of

1173

functions, virtual tables or similar items with each Connection as it is

1174

created. The default value is an empty list. Whenever a Connection is

1175

created, each item in <code>apsw.connection_hooks</code> is invoked with

1176

a single parameter being the new Connection object. If the hook raises an

1177

exception then the creation of the Connection fails.

1214

The purpose of the hooks is to allow the easy registration of functions, virtual tables or similar items with

1215

each Connection as it is created. The default value is an empty list. Whenever a Connection is created, each item

1216

in <code>apsw.connection_hooks</code> is invoked with a single parameter being the new Connection object. If the

1217

hook raises an exception then the creation of the Connection fails.

1178

1218

</dd>

1179

1219

</dl>

1180

1220

1181

<h2><a name="Module_constants" id="Module_constants">Module

1182

constants</a></h2>

1221

<h2><a name="Module_constants" id="Module_constants">Module constants</a></h2>

1183

1222

1184

Note that the same values can be used in different contexts. For example

1185

SQLITE_OK and SQLITE_CREATE_INDEX both have a value of zero. For each group

1186

of constants there is also a mapping (dict) available that you can supply a

1187

string to and get the corresponding numeric value, or supply a numeric value

1188

and get the corresponding string. These can help improve diagnostics/logging,

1189

calling other modules etc. For example

1223

Note that the same values can be used in different contexts. For example SQLITE_OK and SQLITE_CREATE_INDEX both

1224

have a value of zero. For each group of constants there is also a mapping (dict) available that you can supply a

1225

string to and get the corresponding numeric value, or supply a numeric value and get the corresponding string. These

1226

can help improve diagnostics/logging, calling other modules etc. For example

1190

1227

1191

1228

1192

1229

<pre>

1193

1230

apsw.mapping_authorizer_function["SQLITE_READ"]=20

1194

1231

apsw.mapping_authorizer_function[20]="SQLITE_READ"

1232

1195

1233

</pre>

1196

1234

</blockquote>

1197

1235

1199

1237

<dt>SQLITE_VERSION_NUMBER</dt>

1200

1238

1201

1239

<dd>

1202

This is the version of the SQLite library from the header file

1203

<tt>sqlite3.h</tt> at apsw compile time. It is expressed as an integer

1204

(eg 3003010 for SQLite 3.3.10).

1240

This is the version of the SQLite library from the header file <tt>sqlite3.h</tt> at apsw compile time. It is

1241

expressed as an integer (eg 3003010 for SQLite 3.3.10).

1205

1242

</dd>

1206

1243

1207

<dt>SQLITE_OK, SQLITE_DENY, SQLITE_IGNORE</dt>

1244

<dt><a href="http://sqlite.org/c3ref/c_deny.html">SQLITE_OK, SQLITE_DENY, SQLITE_IGNORE</a></dt>

1208

1245

1209

1246

<dd>

1210

1247

These values are returned from authorizer functions. Mapping is

1211

1248

<code>apsw.mapping_authorizer_return</code>

1212

1249

</dd>

1213

1250

1214

<dt>SQLITE_CREATE_INDEX, SQLITE_CREATE_TABLE and 30

1215

others</dt>

1251

<dt><a href="http://sqlite.org/c3ref/c_alter_table.html">SQLITE_CREATE_INDEX, SQLITE_CREATE_TABLE

1252

and 30 others</a></dt>

1216

1253

1217

1254

<dd>

1218

The values are passed to authorizer functions. You can see a complete

1219

list in the <a href=

1220

"http://sqlite.org/capi3ref.html#sqlite3_set_authorizer">sqlite3_set_authorizer

1221

documentation</a>. Mapping is

1255

The values are passed to authorizer functions. You can see a complete list in the <a href=

1256

"http://sqlite.org/c3ref/set_authorizer.html">sqlite3_set_authorizer documentation</a>. Mapping is

1222

1257

<code>apsw.mapping_authorizer_function</code>

1223

1258

</dd>

1224

1259

1225

<dt>SQLITE_INDEX_CONSTRAINT_EQ, SQLITE_INDEX_CONSTRAINT_GT

1226

and 4 others</dt>

1260

<dt>SQLITE_INDEX_CONSTRAINT_EQ, SQLITE_INDEX_CONSTRAINT_GT and 4 others</dt>

1227

1261

1228

1262

<dd>

1229

These values are passed to the BestIndex method of <a href=

1230

"#VirtualTables">virtual tables</a>. Mapping is

1263

These values are passed to the BestIndex method of <a href="#VirtualTables">virtual tables</a>. Mapping is

1231

1264

<code>apsw.mapping_bestindex_constraints</code>

1232

1265

</dd>

1233

1266

1234

<dt>SQLITE_IOERR_READ, SQLITE_IOERR_FSYNC and 7

1235

others</dt>

1236

1237

<dd>

1238

These are <a href=

1239

"http://www.sqlite.org/cvstrac/wiki?p=ExtendedResultCodes">extended

1240

result codes</a> which are provided in <a href=

1241

"#Exceptions">exceptions</a>. Mapping is

1242

<code>apsw.mapping_extended_result_codes</code>

1243

</dd>

1244

1245

<dt>SQLITE_OK, SQLITE_ERROR, SQLITE_PERM and 22

1246

others</dt>

1247

1248

<dd>

1249

These are <a href=

1250

"http://sqlite.org/capi3ref.html#result-codes">SQLite result codes</a>

1251

which are provided in <a href="#Exceptions">exceptions</a>. Mapping is

1252

<code>apsw.mapping_result_codes</code>

1267

<dt><a href="http://sqlite.org/c3ref/c_ioerr_blocked.html">SQLITE_IOERR_READ, SQLITE_IOERR_FSYNC

1268

and 7 others</a></dt>

1269

1270

<dd>

1271

These are <a href="http://sqlite.org/c3ref/c_ioerr_blocked.html">extended result codes</a> which are provided

1272

in <a href="#Exceptions">exceptions</a>. Mapping is <code>apsw.mapping_extended_result_codes</code>

1273

</dd>

1274

1275

<dt><a href="http://sqlite.org/c3ref/c_abort.html">SQLITE_OK, SQLITE_ERROR, SQLITE_PERM and 22

1276

others</a></dt>

1277

1278

<dd>

1279

These are <a href="http://sqlite.org/c3ref/c_abort.html">SQLite result codes</a> which are provided in

1280

<a href="#Exceptions">exceptions</a>. Mapping is <code>apsw.mapping_result_codes</code>

1281

</dd>

1282

1283

<dt><a href="http://sqlite.org/c3ref/c_open_create.html">SQLITE_OPEN_READONLY, SQLITE_OPEN_CREATE

1284

and 10 others</a></dt>

1285

1286

<dd>

1287

These are used for <a href="http://sqlite.org/c3ref/open.html">opening the database</a> as well as the VFS.

1288

Mapping is <code>apsw.mapping_open_flags</code>

1289

</dd>

1290

1291

<dt><a href="http://www.sqlite.org/c3ref/c_limit_attached.html">SQLITE_LIMIT_LENGTH,

1292

SQLITE_LIMIT_SQL_LENGTH and 8 others</a></dt>

1293

1294

<dd>

1295

These are used for getting or setting <a href="http://www.sqlite.org/c3ref/limit.html">run-time limits</a>

1253

1296

</dd>

1254

1297

</dl>

1255

1298

1256

<h2><a name="Connection_class" id="Connection_class">Connection

1257

class</a></h2>

1299

<h2><a name="Connection_class" id="Connection_class">Connection class</a></h2>

1258

1300

1259

1301

The connection class wraps a <code>sqlite3 pointer</code>.

1260

1302

1261

1303

<dl>

1262

<dt>Connection(filename)</dt>

1304

<dt>Connection(filename, flags=SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, vfs=None,

1305

statementcachesize=100)</dt>

1263

1306

1264

1307

<dd>

1265

Opens an SQLite database named <code>filename</code>. (This calls

1266

<a href=

1267

"http://www.sqlite.org/capi3ref.html#sqlite3_open">sqlite3_open</a>

1268

behind the scenes. You must call close when finished with a Connection.

1269

If you do not call close then the destructor will invoke

1270

<code>sys.excepthook</code> which by default will print a traceback.

1308

Opens an SQLite database named <code>filename</code>. (This calls <a href=

1309

"http://sqlite.org/c3ref/open.html">sqlite3_open_v2</a> behind the scenes.) If you don't supply a vfs then the

1310

default will be used. You can also set how many entries there are in the statement cache. You will generally want

1311

this to be the total number of distinct SQL statements you execute frequently.

1271

1312

</dd>

1272

1313

1273

1314

<dt>close(force=False)</dt>

1274

1315

1275

1316

<dd>

1276

You must call this method when you are done with a Connection. Behind

1277

the scenes it calls <a href=

1278

"http://www.sqlite.org/capi3ref.html#sqlite3_close">sqlite3_close</a>. It

1279

is ok to call this method multiple times. Note that this method can raise

1280

an exception. Some examples of when exceptions are raised:

1317

You must call this method when you are done with a Connection. Behind the scenes it calls <a href=

1318

"http://sqlite.org/c3ref/close.html">sqlite3_close</a>. It is ok to call this method multiple times. Note that

1319

this method can raise an exception. Some examples of when exceptions are raised:

1281

1320

1282

1321

<ul>

1283

<li>You have statements still executing (ie currently positioned at a

1284

row). Statements that have consumed all results will not hold up

1285

closing a Connection.</li>

1286

1287

<li>You were in the middle of a transaction. SQLite will attempt to do

1288

a rollback and errors in your rollback hook will be passed back.</li>

1289

1290

<li>Any virtual tables will be dropped/disconnected. Errors in your

1291

Disconnect/Destroy methods will be passed back.</li>

1322

<li>You have statements still executing (ie currently positioned at a row, with more statements to execute).

1323

Statements that have consumed all results will not hold up closing a Connection.</li>

1324

1325

<li>You were in the middle of a transaction. SQLite will attempt to do a rollback and errors in your rollback

1326

hook will be passed back.</li>

1327

1328

<li>Any virtual tables will be dropped/disconnected. Errors in your Disconnect/Destroy methods will be passed

1329

back.</li>

1292

1330

</ul>

1293

1331

1294

If <tt>force</tt> is True, then any executing cursors are forcibly

1295

closed.

1332

If <tt>force</tt> is True, then any executing cursors are forcibly closed.

1296

1333

</dd>

1297

1334

1298

1335

<dt>cursor()</dt>

1299

1336

1300

1337

<dd>

1301

Creates a new <a href="#Cursor">cursor</a> object on this

1302

database.

1338

Creates a new <a href="#Cursor">cursor</a> object on this database.

1303

1339

</dd>

1304

1340

1305

1341

<dt>changes()</dt>

1306

1342

1307

1343

<dd>

1308

This function returns the number of database rows that were changed

1309

(or inserted or deleted) by the most recently completed INSERT, UPDATE,

1310

or DELETE statement. (This calls <a href=

1311

"http://www.sqlite.org/capi3ref.html#sqlite3_changes">sqlite3_changes</a>.

1312

Read that link for some additional notes.)

1344

This function returns the number of database rows that were changed (or inserted or deleted) by the most

1345

recently completed INSERT, UPDATE, or DELETE statement. (This calls <a href=

1346

"http://sqlite.org/c3ref/changes.html">sqlite3_changes</a>. Read that link for some additional notes.)

1313

1347

</dd>

1314

1348

1315

1349

<dt>totalchanges()</dt>

1316

1350

1317

1351

<dd>

1318

This function returns the total number of database rows that have be

1319

modified, inserted, or deleted since the database connection was opened.

1320

(This calls <a href=

1321

"http://www.sqlite.org/capi3ref.html#sqlite3_total_changes">sqlite3_total_changes</a>.

1322

Read that link for some additional notes.)

1352

This function returns the total number of database rows that have be modified, inserted, or deleted since the

1353

database connection was opened. (This calls <a href=

1354

"http://sqlite.org/c3ref/total_changes.html">sqlite3_total_changes</a>. Read that link for some additional

1355

notes.)

1323

1356

</dd>

1324

1357

1325

1358

<dt>last_insert_rowid()</dt>

1326

1359

1327

1360

<dd>

1328

Returns the integer key of the most recent insert in the database.

1329

(This calls <a href=

1330

"http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid">sqlite3_last_insert_rowid</a>.)

1361

Returns the integer key of the most recent insert in the database. (This calls <a href=

1362

"http://sqlite.org/c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid</a>.)

1331

1363

</dd>

1332

1364

1333

1365

<dt>complete(statement)</dt>

1334

1366

1335

1367

<dd>

1336

Calls <a href=

1337

"http://www.sqlite.org/capi3ref.html#sqlite3_complete">sqlite3_complete</a>

1338

which tells you if the input string comprises one or more complete SQL

1339

statements.

1368

Calls <a href="http://sqlite.org/c3ref/complete.html">sqlite3_complete</a> which tells you if the input string

1369

comprises one or more complete SQL statements.

1340

1370

</dd>

1341

1371

1342

1372

<dt>setbusytimeout(milliseconds)</dt>

1343

1373

1344

1374

<dd>

1345

1375

Sets the busy timeout. (This calls <a href=

1346

"http://www.sqlite.org/capi3ref.html#sqlite3_busy_timeout">sqlite3_busy_timeout</a>).

1376

"http://sqlite.org/c3ref/busy_timeout.html">sqlite3_busy_timeout</a>).

1347

1377

</dd>

1348

1378

1349

1379

<dt>setbusyhandler(callable)</dt>

1350

1380

1351

1381

<dd>

1352

Sets the busy handler to callable. callable will be called with one

1353

integer argument which is the number of prior calls to the busy callback

1354

for the same lock. If the busy callback returns something that evaluates

1355

to False, then SQLite returns SQLITE_BUSY to the calling code.. If the

1356

callback returns something that evaluates to True, then SQLite tries to

1357

open the table again and the cycle repeats. (This calls <a href=

1358

"http://www.sqlite.org/capi3ref.html#sqlite3_busy_handler">sqlite3_busy_handler</a>).

1382

Sets the busy handler to callable. callable will be called with one integer argument which is the number of

1383

prior calls to the busy callback for the same lock. If the busy callback returns something that evaluates to

1384

False, then SQLite returns SQLITE_BUSY to the calling code.. If the callback returns something that evaluates to

1385

True, then SQLite tries to open the table again and the cycle repeats. (This calls <a href=

1386

"http://sqlite.org/c3ref/busy_handler.html">sqlite3_busy_handler</a>).

1359

1387

</dd>

1360

1388

1361

1389

<dt>interrupt()</dt>

1362

1390

1363

1391

<dd>

1364

Causes any pending operations on the database to abort at the earliest

1365

opportunity. You can call this from any thread. (This calls <a href=

1366

"http://www.sqlite.org/capi3ref.html#sqlite3_interrupt">sqlite3_interrupt</a>).

1392

Causes any pending operations on the database to abort at the earliest opportunity. You can call this from any

1393

thread. (This calls <a href="http://sqlite.org/c3ref/interrupt.html">sqlite3_interrupt</a>).

1394

</dd>

1395

1396

<dt><a name="Connection_limit" id="Connection_limit">limit(id [, newval])</a></dt>

1397

1398

<dd>

1399

If called with one parameter then the relevant limit is returned. If called with two then the limit is

1400

changed. Returns the old value of the limit. This calls <a href=

1401

"http://www.sqlite.org/c3ref/limit.html">sqlite3_limit</a>. For example

1402

<code>connection.limit(apsw.SQLITE_LIMIT_LENGTH, 1024)</code> would set the limit for blobs and strings to 1

1403

kilobyte. To find the maximum value, set the limit to 0x7fffffff and then call <tt>connection.limit</tt> again to

1404

see what value it was set to.

1367

1405

</dd>

1368

1406

1369

1407

<dt>createscalarfunction(name, callable, numargs=-1)</dt>

1370

1408

1371

1409

<dd>

1372

Registers a scalar function. The callable will be called. You can

1373

specify how many arguments your function takes as the numargs parameter

1374

or supply -1 to take any amount. (This calls <a href=

1375

"http://www.sqlite.org/capi3ref.html#sqlite3_create_function">sqlite3_create_function</a>).

1410

Registers a scalar function. The callable will be called. You can specify how many arguments your function

1411

takes as the numargs parameter or supply -1 to take any amount. (This calls <a href=

1412

"http://sqlite.org/c3ref/create_function.html">sqlite3_create_function</a>).

1376

1413

</dd>

1377

1414

1378

<dt>createaggregatefunction(name, factorycallback,

1379

numargs=-1)</dt>

1415

<dt>createaggregatefunction(name, factorycallback, numargs=-1)</dt>

1380

1416

1381

1417

<dd>

1382

1418

Registers an aggregate function. (This calls <a href=

1383

"http://www.sqlite.org/capi3ref.html#sqlite3_create_function">sqlite3_create_function</a>.)

1384

You can specify how many arguments your function takes as the numargs

1385

parameter or supply -1 to take any amount. When the function is called by

1386

an SQL query, the factorycallback is called without any arguments. The

1387

factorycallback needs to return a tuple consisting of three 3 items.

1419

"http://sqlite.org/c3ref/create_function.html">sqlite3_create_function</a>.) You can specify how many arguments

1420

your function takes as the numargs parameter or supply -1 to take any amount. When the function is called by an

1421

SQL query, the factorycallback is called without any arguments. The factorycallback needs to return a tuple

1422

consisting of three 3 items.

1388

1423

1389

1424

<ul>

1390

1425

<li>

1392

1427

</li>

1393

1428

1394

1429

<li>

1395

a step function which is called for each row. The context object

1396

will be the first parameter, and the remaining parameters will be

1397

from the SQL statement. The return value is ignored - you supply it

1398

in final.

1430

a step function which is called for each row. The context object will be the first parameter, and the

1431

remaining parameters will be from the SQL statement. The return value is ignored - you supply it in

1432

final.

1399

1433

</li>

1400

1434

1401

1435

<li>

1402

a final function which is called at the end. The only parameter

1403

will be the context object. The value returned is set as the return

1404

for the function. It must be a valid SQLite type. Note that the final

1405

function is always called even if an exception was raised by the step

1406

function. This allows you to ensure any resources are cleaned up.

1436

a final function which is called at the end. The only parameter will be the context object. The value

1437

returned is set as the return for the function. It must be a valid SQLite type. Note that the final function

1438

is always called even if an exception was raised by the step function. This allows you to ensure any

1439

resources are cleaned up.

1407

1440

</li>

1408

1441

</ul>

1409

1442

</dd>

1411

1444

<dt>createcollation(name, callable)</dt>

1412

1445

1413

1446

<dd>

1414

Creates a collation with the specified name and callable. The callable

1415

will be passed two string arguments. It should return -1 if the first is

1416

less than the second, 0 if they are equal and 1 and if the first is

1417

greater than the second. Note that this controls sorting (ORDER BY in

1418

SQL) so your comparisons don't affect other SQL operations. Read more

1419

about <a href="http://www.sqlite.org/datatype3.html#collation">SQLite's

1420

handling of collations.</a> (This calls <a href=

1421

"http://www.sqlite.org/capi3ref.html#sqlite3_create_collation">sqlite3_create_collation</a>.)

1422

If there is an error in your Python code then 0 (ie items are equal) is

1423

returned.

1447

Creates a collation with the specified name and callable. The callable will be passed two string arguments. It

1448

should return -1 if the first is less than the second, 0 if they are equal and 1 and if the first is greater than

1449

the second. Note that this controls sorting (ORDER BY in SQL) so your comparisons don't affect other SQL

1450

operations. Read more about <a href="http://www.sqlite.org/datatype3.html#collation">SQLite's handling of

1451

collations.</a> (This calls <a href=

1452

"http://sqlite.org/c3ref/create_collation.html">sqlite3_create_collation</a>.) If there is an error in your

1453

Python code then 0 (ie items are equal) is returned.

1454

</dd>

1455

1456

<dt>collationneeded(callable)</dt>

1457

1458

<dd>

1459

<tt>callable</tt> will be called if a statement requires a collation that hasn't been registered. Your

1460

callable will be passed two parameters. The first is the connection object. The second is the name of the

1461

collation. If you have the collation code available then call createcollation, else just return.

1424

1462

</dd>

1425

1463

1426

1464

<dt>setauthorizer(callable)</dt>

1427

1465

1428

1466

<dd>

1429

The callable is invoked while SQL statements are being prepared. The

1430

intent is to allow applications to safely execute user entered SQL. The

1431

callable is called with 5 parameters:

1467

The callable is invoked while SQL statements are being prepared. The intent is to allow applications to safely

1468

execute user entered SQL. The callable is called with 5 parameters:

1432

1469

1433

1470

<ul>

1434

1471

<li>

1435

an integer representing the operation (the constants are available

1436

on the apsw module - eg <code>apsw.SQLITE_CREATE_TABLE</code>.

1472

an integer representing the operation (the constants are available on the apsw module - eg

1473

<code>apsw.SQLITE_CREATE_TABLE</code>.

1437

1474

</li>

1438

1475

1439

1476

<li>

1449

1486

</li>

1450

1487

1451

1488

<li>

1452

Name of the innermost trigger or view doing the access (or

1453

None)

1489

Name of the innermost trigger or view doing the access (or None)

1454

1490

</li>

1455

1491

</ul>

1456

1492

1457

You should return <code>apsw.SQLITE_OK</code> to allow the operation

1458

or <code>apsw.SQLITE_DENY</code> or <code>apsw.SQLITE_IGNORE</code> as

1459

applicable. (SQLITE_DENY is returned if there is an error in your Python

1493

You should return <code>apsw.SQLITE_OK</code> to allow the operation or <code>apsw.SQLITE_DENY</code> or

1494

<code>apsw.SQLITE_IGNORE</code> as applicable. (SQLITE_DENY is returned if there is an error in your Python

1460

1495

code).

1461

1496

1462

This calls <a href=

1463

"http://www.sqlite.org/capi3ref.html#sqlite3_set_authorizer">sqlite3_set_authorizer</a>

1464

which contains more detailed documentation.

1497

This calls <a href="http://sqlite.org/c3ref/set_authorizer.html">sqlite3_set_authorizer</a> which contains

1498