Simple python script to import Italian comuni and province associated from official CVS list of Istat website.

provincialize.py

# Provincialize - Simple script to import Italian comuni and provincie associated from
# official CVS list of Comuni Italiani downloaded from Istat web site:
# http://www.istat.it/it/archivio/6789

import csv, sys
try:
	import MySQLdb 
except:
	print 'Run \'sudo apt-get install python-mysqldb\''
	sys.exit(1)

comuni_path = 'elenco_comuni_italiani_30_giugno_2012.csv'
mysql_host = 'localhost'
mysql_user = 'user'
mysql_pwd = 'password'
mysql_db = 'db'

query_cp_parameters = """INSERT INTO cities(name,province) VALUES (%s,%s);"""
query_p_parameters = """INSERT INTO province(name) VALUES (%s);"""

dbms=MySQLdb.connect(host=mysql_host,user=mysql_user, passwd=mysql_pwd, db=mysql_db)
dbms.set_character_set('utf8')
db = dbms.cursor()
db.execute('SET NAMES utf8;')
db.execute('SET CHARACTER SET utf8;')
db.execute('SET character_set_connection=utf8;')

comuni_list = csv.reader(open(comuni_path, 'rb'), delimiter=';')
for comune in comuni_list:
	comune_name = comune[7]
	comune_provincia = ' '.join([w.capitalize() for w in comune[16].split(' ')])

	if comune[0].isdigit() and comune_name and comune_provincia:

		print query_cp_parameters % (comune_name, comune_provincia)
		db.execute(query_cp_parameters, (comune_name, comune_provincia))
		print query_p_parameters % (comune_provincia)
		db.execute(query_p_parameters, (comune_provincia))