This is the first in a series of posts about database access with
IronPython and ADO.NET. This post will discuss connecting to the database and executing basic DDL and SQL statements. So that the examples can run on Windows and non Windows systems, they will support either SQLite3 via the Mono.Data.SQLiteCilent ADO provider or Microsoft Access via the System.Data.Odbc provider.
Firstly we need some data to play with. A number of Python Web frameworks have been adding support for displaying the flag of the country against weblog comments. The country is identified from the remote IP address of the users browser. So we use IronPython to create a table and load it with the data from the ip-to-country cvs file which can be download from
here. The full source of the IronPython script is
here.
Creating a table
The first section of code references and imports the assemblies required for connecting and accessing the database. So the script can support either SQLite or Access, it tries to reference and import the SQLite ADO provider first, if this fails it then attempts to import the ODBC provider. The scripts uses an import alias so that we can refer to the database connection method by the same name independant of what ADO provider is being used. Also the database specific connection strings and table creation statements are defined here.
import clr
import System
clr.AddReference("System.Data")
import System.Data
try:
clr.AddReference("Mono.Data.SqliteClient")
from Mono.Data.SqliteClient import SqliteConnection as dbconnection
connectstr = 'URI=file:ip2country.db,version=3'
ip2country_create_table_ddl = '''
CREATE TABLE ip2country (
ipfrom INTEGER,
ipto INTEGER,
countrycode2 CHAR(2),
countrycode3 CHAR(3),
countryname VARCHAR(50),
PRIMARY KEY (ipfrom,ipto)
)
'''
except:
from System.Data.Odbc import OdbcConnection as dbconnection
connectstr = 'DSN=ip2country'
ip2country_create_table_ddl = '''
CREATE TABLE ip2country (
ipfrom DOUBLE,
ipto DOUBLE,
countrycode2 CHAR(2),
countrycode3 CHAR(3),
countryname VARCHAR(50),
CONSTRAINT ip2country_pk PRIMARY KEY (ipfrom,ipto)
)
'''
The last section of the script, connects to the database, opens the connection and creates the table ip2country using the database specific DDL.
dbcon = dbconnection(connectstr)
dbcon.Open()
dbcmd = dbcon.CreateCommand()
dbcmd.CommandText = ip2country_create_table_ddl
dbcmd.ExecuteNonQuery()
dbcon.Close()
So once you run the script, you will either have an SQLite database ip2country.db or Access database ip2country.mdb with a single empty table called ip2country.
Load the data
The full source of the IronPython script for loading the CSV data can be found
here. We use the same code from the previous script to setup access to the data. The ip-to-country.csv file contains data of the following format:
"33996344","33996351","GB","GBR","UNITED KINGDOM"
"50331648","69956103","US","USA","UNITED STATES"
"69956104","69956111","BM","BMU","BERMUDA"
To parse each line of the csv file, a regular expression is used.
import re
re_csv = re.compile(',(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))')
Rather than dynamically creating the SQL insert statement using string concatenation, the script uses variable placeholders in the insert statement and data parameters. In theory, this should be more efficent with the database only needing to prepare the insert statement once, and then binding the data parameters on each insert. But not sure if SQLite or Access does this type of optimisation. At least it means there will be no problems with country names like COTE D'IVOIRE that contain quotes.
dbcmd = dbcon.CreateCommand()
insert_statement= '''
INSERT INTO ip2country (
ipfrom, ipto, countrycode2, countrycode3, countryname
) VALUES ( ?,?,?,?,? )
'''
# Create empty parameters for insert and attach to db command
p1 = dbparam()
dbcmd.Parameters.Add(p1)
p2 = dbparam()
dbcmd.Parameters.Add(p2)
p3 = dbparam()
dbcmd.Parameters.Add(p3)
p4 = dbparam()
dbcmd.Parameters.Add(p4)
p5 = dbparam()
dbcmd.Parameters.Add(p5)
dbcmd.CommandText = insert_statement
Next the script opens the csv file and reads it line by line. After removing the line separator(s), the line is split into individual fields using the compiled regular expression. The value of each field is then assigned to the insert parameter with the delimiting double quotes removed. And the data is inserted into the ip2country table by calling the ExecuteNonQuery Method.
f = open("ip-to-country.csv")
print "Loading..."
for line in f.readlines():
if line.endswith("\r\n"):
line = line[:-2] # running on a posix platform so remove \r\n
else:
line = line[:-1] # must be windows, just remove \n
print line
ipf, ipt, cc2, cc3, cn = re_csv.split(line)
p1.Value = ipf[1:-1]
p2.Value = ipt[1:-1]
p3.Value = cc2[1:-1]
p4.Value = cc3[1:-1]
p5.Value = cn[1:-1]
dbcmd.ExecuteNonQuery()
f.close()
dbcon.Close()To run the script the ip-to-country.csv file must be in the current directory, and since it contains 65,000+ lines of data, it will take a while to run.
ipy.exe load_op2country.py
Select some data
Now the ip2country table should contain some data we can query. Let's create a simple script that when passed an IP address, it prints the country location of the IP address. The source of the script can be found
here.
To find the location, the script first converts the IP address to the numeric equivalent used in the ip2country data using the function ip2number. A SQL select statement is defined using the numeric ip address as the bounds for the where clause. Then an ExecuteReader instance is created and the results processed in a while loop.
dbcon.Open()
dbcmd = dbcon.CreateCommand()
try:
ipaddress = sys.argv[1]
# Convert dotted ip address to number
ipnumber = ip2number(ipaddress)
except:
print "Error - An IP Address is required"
sys.exit(1)
dbcmd.CommandText = '''
SELECT * FROM ip2country
WHERE ipfrom <= %s
AND ipto >= %s
''' % (ipnumber, ipnumber)
reader = dbcmd.ExecuteReader()
while reader.Read():
print "The location of IP address %s is %s." % (ipaddress, reader[4])
reader.Close()
dbcon.Close()
The country name is accessed from row result by column number. I would prefer to get the value of the column via it's name e.g.
print "The location of IP address %s is %s." % (ipaddress, row['countryname'])
and this problem has been addressed by a Greg Stein's
dtuple Python module. You will find a version of the find location script that uses dtuple
here.
Hopefully this post has given you some insight in how to use IronPython with ADO.Net.
