Importing notes from sites

[BatsShadow]

I would like to import all of my poker stars notes. I can parse the stars file. Can someone give me a quick overview into what table I need to put them in and any required fields or special formatting required. Thanks.

[kraada]

This feature is planned but not yet available in PT3.

If you're going to be writing a program to insert them into the PostgreSQL database manually (or writing a program to do it for you), the notes table contains all of the notes. id_note is an incremental count and each note needs a unique id_note (if you start from the number after the last one that would work fun; select count(*) from notes; to see how many notes there currently are.

Now if these are player notes, id_x is going to be id_player for whatever player you are putting the note on. You can find someone's id_player by searching for their name in the player table and joining as necessary. enum_type for a player note will be 'P', date_note is a timestamp of when the note was created; you should be able to use postgres time functions (like now()) and unless you have dates on your old notes that might be best. Then the 'notes' field contains the actual text of the note.

If you do end up writing a program to do this, we would greatly appreciate it if you could share it with the community.

[BatsShadow]

Thanks for the info. If I do write something, it will probably be a python script with no UI so maybe not useful to most of the user base. But I will definitely let it out if I do it.

[BatsShadow]

I got un-lazy and opened up my postgres tool to look at the database. I'm curious - why isn't the development team using database sequences to generate the integer ids (like id_note) instead of using a count() query? It should be much more efficient and is completely managed by the db.

[BatsShadow]

Ok, here it is. I run Postgres on a Linux machine so I ran it like this:

- copy notes and python script into some directory
- make it executable, then run the command:
./pt_notes_import.py -d PT3_2008_12_16_231503 -u dbuser -p 'dbpass' -f notes.txt

NOTES and LIMITATIONS:
- It was written for stars notes, but it has infrastructure defined to easily add support for other sites
- Even though the stars notes are stored in UTF-16, this turns them into ASCII and throws out any non-standard characters. The PT3 db doesn't appear to support Unicode anyway.
- It requires the psycopg2 python module to connect to Postgres. I'm currently running Python 2.5.2
- It only connects to Postgres on the localhost. This could be easily added.
- You could mess up your notes if you run it while PT is running.
- It doesn't do any sort of duplicate checking. If you run it twice, you'll have duplicate notes.
- Really, it doesn't do much error checking at all. For example, I don't know what happens if you have two players with the same name

Code: Select all

#!/usr/bin/python
#
# pt_notes_import.py
#
# Script to import poker site notes into PT3 database

import psycopg2   
import sys
import getopt
import codecs
import re

def Usage():
   print("Usage:\n\t%s --database {dbname} --user {username} --password {password} [--site {stars|??}] --file {notes_file}" % sys.argv[0])


def Main(argv):
   """Main program execution"""
   db, user, password, site, file = ReadArgs(argv)
   conn = GetDbConnection(db, user, password)
   notes = ReadNotes(site, file)
   WriteNotes(conn, notes)


def ReadNotes(site, file):
   """Generator function which yields (user, note) tuples"""
   if 'stars' == site:
      # stars notes are in utf-16 so we have to decode the data
      f = codecs.open(file, encoding='utf-16')
      player = None
      regex = re.compile(r"\[player=(.*)\]")
      note = []

      for line in f:
         m = regex.match(line)
         if m:
            # We found a new player.
            # If player was previously set, return their notes
            if player:
               yield player.encode('ascii', 'ignore'), ''.join(note).strip()
               # reset note
               note = []
            # Remember the player
            player = m.group(1)

         # If this isn't a player line, then it's a note.  Save it
         elif player:
            note.append(line.encode('ascii', 'ignore'))

   #if 'fulltilt' == site:
   #  ...

   else:
      print "Error: Unsupported site format '%s'" % site
      sys.exit()


def WriteNotes(conn, notes):
   """Given notes of the form (player, note),
      appends them to the PT notes table using the connection conn"""
   cur = conn.cursor();
   insert = """INSERT INTO notes (id_note, id_x, enum_type, date_note, notes)
      SELECT n, id_player, 'P', now(), %(note)s FROM player,
         (SELECT COUNT(*)+1 AS n FROM notes) n
      WHERE player_name = %(player)s"""

   for player, note in notes:
      params = {'player':player, 'note':note}
      cur.execute(insert, params)

   conn.commit()
   cur.close()


def GetDbConnection(db, user, password):
   """Returns a db connection object"""
   try:
      connString = "dbname='%s' user='%s' password='%s'" % (db, user, password)
      conn = psycopg2.connect(connString)
      return conn

   except Exception, e:
      print "Error: Unable to connect to the database"
      print e
      sys.exit()


def ReadArgs(argv):
   """Parses command line arguments"""
   shortArgDef = "d:u:p:s:f:"
   longArgDef = ["database=", "user=", "password=", "site=", "file="]

   db = None
   user = None
   password = None
   site = 'stars'
   file = None

   try:
      opts, args = getopt.getopt(argv, shortArgDef, longArgDef)
      for o, a in opts:
         if o in ('-d', '--database'):
            db = a
         if o in ('-u', '--user'):
            user = a
         if o in ('-p', '--password'):
            password = a
         if o in ('-s', '--site'):
            site = a
         if o in ('-f', '--file'):
            file = a

      if not db or not user or not password or not site or not file:
         Usage()
         sys.exit()
      return db, user, password, site, file

   except getopt.GetoptError:
      Usage()
      sys.exit()


# Call main
if __name__ == "__main__":
   Main(sys.argv[1:])


[kraada]

Thanks! You get extra props for writing in my favorite language too :)

I'll make sure the mention this to anybody else looking for similar functionality.

And just to be sure: you wouldn't mind me extending this to include importing FT notes and some basic duplicate checking so it could be re-run as needed?

[BatsShadow]

I didn't feel like littering the thing with comments or packaging/zipping it, etc, but I'd prefer if it was GPL. Extend away! :D

[kraada]

Thanks, I'll treat it as if it were GPLed. I don't know exactly when I'll have a chance to look into this, but I'll definitely take a look and see what I can do :)