Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Flickr stats to SQLite Converter

By: a guest on Apr 27th, 2010  |  syntax: Python  |  size: 3.05 KB  |  hits: 319  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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]) )
create a new version of this paste RAW Paste Data