Another fun weekend project, moving my word data over to a Postgres Database for faster search suggestions.

populate_postgres_search_feature.py

import psycopg2
import pgdb
from py2neo import authenticate, Graph, Node, Relationship
import os

p_connection = psycopg2.connect(host = 'localhost',
user='USER',
password='SECRET',
dbname='lexinomicon')

authenticate('localhost:7474', 'USER', 'SECRET')
graph_db = Graph('http://localhost:7474/db/data')

cursor = p_connection.cursor()

os.system('clear')
print 'Acquiring Distinct Results From Graph DB'
print '-'*25

#Get all the unique node names from the graph db
cypher_string = 'MATCH (w:Word) WITH w ORDER BY LOWER(w.name) RETURN COLLECT(DISTINCT w.name)'
unique_names = graph_db.cypher.execute(cypher_string)

#Drop the table on the postgres layer
cursor.execute('DROP TABLE IF EXISTS search_item;')
p_connection.commit()

#Recreate the table in the postgres layer
cursor.execute('''
    CREATE TABLE search_item(
    id SERIAL PRIMARY KEY,
    name varchar(255) NOT NULL UNIQUE,
    number_of_hits integer,
    popularity_index real
    );
''')
p_connection.commit()

#Add all the unique node names to the postgres layer
for name in unique_names[0][0]:
    #Tell the user where we are at...
    os.system('clear')
    print name
    print '-'*25

    cursor.execute("INSERT INTO search_item (name) VALUES (%(name_string)s);", {'name_string': name})
    p_connection.commit()

#Tell the user where we are at...
os.system('clear')
print 'Adding Index to Name Column'
print '-'*25

cursor.execute('CREATE INDEX search_index ON search_item (name ASC);')
p_connection.commit()

p_connection.close()