Guest User

Flickr stats to SQLite Converter

a guest
Apr 27th, 2010
419
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/usr/bin/env python
  2.  
  3. # Script for importing Flickr logs into an SQLite DB
  4. #  by Kadin2048, http://kadin.sdf-us.org/
  5. # Requires Python 2.5+ or (Python <2.5 and PySQLite)
  6.  
  7. # Usage: flickr_sqlite.py YYYY_MM_daily.csv
  8. #    or: flickr_sqlite.py YYYY_MM_monthly.csv
  9. #    or: for i in *.csv; do flickr_sqlite.py $i; done
  10.  
  11. import os.path
  12. import sys
  13. import time
  14. import csv
  15. import sqlite3
  16.  
  17. # The SQLite file to use or create
  18. DATABASE = 'flickrstats.sqlite'
  19.  
  20. # Logging:
  21. #LOG = sys.stderr
  22. LOG = open('flickrstats-import.log','a')
  23.  
  24. def log(message):
  25.     LOG.write('[' + time.ctime() + ']: ' + message + '\n')
  26.  
  27.  
  28. def main(inputfilename):
  29.     log('Reading from ' + inputfilename)
  30.    
  31.     basename = os.path.basename(inputfilename)
  32.     log('Basename is ' + basename)
  33.    
  34.     csvreader = csv.reader( open(inputfilename, 'r')  )
  35.     L = []
  36.     c = 0
  37.     for i in csvreader:
  38.         L.append(i)
  39.         c = c+1
  40.     log("Successfully read %s lines from file." % c)
  41.    
  42.     L.pop(0)  # drop header row
  43.    
  44.     log("Have %s rows of data." % len(L))
  45.    
  46.     connection, cursor = setupTable(DATABASE)  # create the table in the database file, if it doesn't exist
  47.    
  48.     if (basename.find('monthly') >= 0):
  49.         writeMonthly(connection, cursor, L)
  50.     elif (basename.find('daily') >= 0):
  51.         writeDaily(connection, cursor, L)
  52.     else:
  53.         log("Doesn't look like a daily or monthly file. Nothing more to do.")
  54.         return 1
  55.    
  56.     return 0
  57.  
  58.  
  59. def setupTable(databasefile):
  60.     """Create a table to hold Flickr referrer data.
  61.     Returns a Connection and Cursor."""
  62.    
  63.     # Create a 'Connection' and 'Cursor'
  64.     connection = sqlite3.connect(databasefile)
  65.     cursor = connection.cursor()
  66.    
  67.     # Create a table
  68.     log("Attempting to create daily table, if it doesn't exist.")
  69.     cursor.execute('''CREATE TABLE IF NOT EXISTS flickrdaily ("date" TEXT, "flickrPage" TEXT, "fullReferrer" TEXT, "referrerDomain" TEXT, "searchTerm" TEXT, "count" INTEGER)''')
  70.    
  71.     log("Attempting to create monthly table, if it doesn't exist.")
  72.     cursor.execute('''CREATE TABLE IF NOT EXISTS flickrmonthly ("date" TEXT, "flickrPage" TEXT, "fullReferrer" TEXT, "referrerDomain" TEXT, "searchTerm" TEXT, "count" INTEGER)''')
  73.    
  74.     return connection, cursor
  75.  
  76.  
  77. def writeDaily(connection, cursor, datalist):
  78.     log("Writing to daily table.")
  79.    
  80.     c = 0
  81.     for row in datalist:
  82.         cols = tuple(row)
  83.         #log("Inserting " + str(cols))
  84.         cursor.execute('''INSERT INTO flickrdaily ("date", "flickrPage", "fullReferrer", "referrerDomain", "searchTerm", "count") VALUES (?, ?, ?, ?, ?, ?)''', cols)
  85.         c = c+1
  86.    
  87.     # And commit.
  88.     connection.commit()
  89.     log("Committed %s rows!" % c )
  90.    
  91.     return
  92.  
  93.  
  94. def writeMonthly(connection, cursor, datalist):
  95.     log("Writing to monthly table.")
  96.    
  97.     c = 0
  98.     for row in datalist:
  99.         cols = tuple(row)
  100.         #log("Inserting " + str(cols))
  101.         cursor.execute('''INSERT INTO flickrmonthly ("date", "flickrPage", "fullReferrer", "referrerDomain", "searchTerm", "count") VALUES (?, ?, ?, ?, ?, ?)''', cols)
  102.         c = c+1
  103.    
  104.     # And commit.
  105.     connection.commit()
  106.     log("Committed %s rows!\n" % c )
  107.    
  108.     return
  109.  
  110.  
  111. # Interactive mode
  112. if __name__ == "__main__":
  113.     sys.exit( main(sys.argv[1]) )
RAW Paste Data