In [ 45 ]: import datetime as dt
A single data row can be written with the respective SQL statement, as follows:
In [ 46 ]: con.execute(‘INSERT INTO numbs VALUES(?, ?, ?)’,
(dt.datetime.now(), 0.12, 7.3))
Out[46]: <sqlite3.Cursor at 0xb8a4570>
However, you usually have to (or want to) write a larger data set in bulk:
In [ 47 ]: data = np.random.standard_normal(( 10000 , 2 )).round( 5 )
In [ 48 ]: for row in data:
con.execute(‘INSERT INTO numbs VALUES(?, ?, ?)’,
(dt.datetime.now(), row[ 0 ], row[ 1 ]))
con.commit()
There is also a method called executemany. Since we have combined current date-time
information with our pseudorandom number data set, we cannot use it here. What we can
use, however, is fetchmany to retrieve a certain number of rows at once from the database:
In [ 49 ]: con.execute(‘SELECT * FROM numbs’).fetchmany( 10 )
Out[49]: [(u‘2014-09-28 15:16:19.486021’, 0.12, 7.3),
(u‘2014-09-28 15:16:19.762476’, 0.30736, -0.21114),
(u‘2014-09-28 15:16:19.762640’, 0.95078, 0.50106),
(u‘2014-09-28 15:16:19.762702’, 0.95896, 0.15812),
(u‘2014-09-28 15:16:19.762774’, -0.42919, -1.45132),
(u‘2014-09-28 15:16:19.762825’, -0.99502, -0.91755),
(u‘2014-09-28 15:16:19.762862’, 0.25416, -0.85317),
(u‘2014-09-28 15:16:19.762890’, -0.55879, -0.36144),
(u‘2014-09-28 15:16:19.762918’, -1.61041, -1.29589),
(u‘2014-09-28 15:16:19.762945’, -2.04225, 0.43446)]
Or we can just read a single data row at a time:
In [ 50 ]: pointer = con.execute(‘SELECT * FROM numbs’)
In [ 51 ]: for i in range( 3 ):
print pointer.fetchone()
Out[51]: (u‘2014-09-28 15:16:19.486021’, 0.12, 7.3)
(u‘2014-09-28 15:16:19.762476’, 0.30736, -0.21114)
(u‘2014-09-28 15:16:19.762640’, 0.95078, 0.50106)
In [ 52 ]: con.close()
!rm -f $path*
SQL databases are a rather broad topic; indeed, too broad and complex to be covered in any
significant way in this chapter. The basic messages only are:
Python integrates pretty well with almost any database technology.
The basic SQL syntax is mainly determined by the database in use; the rest is, as we
say, real Pythonic.
Writing and Reading NumPy Arrays
NumPy itself has functions to write and read ndarray objects in a convenient and
performant fashion. This saves a lot of effort in some circumstances, such as when you
have to convert NumPy dtypes into specific database types (e.g., for SQLite3). To illustrate
that NumPy can sometimes be an efficient replacement for a SQL-based approach, we
replicate the example from before, this time only using NumPy:
In [ 53 ]: import numpy as np
Instead of pandas, we use the arange function of NumPy to generate an array object with
datetime objects stored:
[ 30 ]
