I'm creating a crawler app that will always, in the beginning, when the Class constructor, ensure that the basic database structure exists.
Is this a bad practice? What is the advantage OS creating the structure directly on the database?
import psycopg2
from datetime import timedelta
class DBManager:
def __init__(self):
self.connected = False
self.conn = None
self.cursor = None
try:
self.conn = psycopg2.connect()
self.cursor = self.conn.cursor()
except:
print "I am unable to connect to the database"
try:
self.cursor.execute("BEGIN TRANSACTION;")
self.cursor.execute('CREATE TABLE IF NOT EXISTS proxy(id SERIAL NOT NULL PRIMARY KEY,ip VARCHAR(15) NOT NULL,active BOOLEAN NOT NULL, times_used smallint default 0, last_use timestamp DEFAULT CURRENT_TIMESTAMP not null );')
self.cursor.execute('CREATE TABLE IF NOT EXISTS app_update (time TIMESTAMP NOT NULL);')
self.cursor.execute("""
CREATE OR REPLACE FUNCTION merge_proxy(_proxy VARCHAR(15)) RETURNS void AS $$
BEGIN
IF EXISTS( SELECT * FROM proxy WHERE ip = _proxy ) THEN
UPDATE proxy SET active = TRUE WHERE ip = _proxy;
ELSE
INSERT INTO proxy(ip,active,times_used,last_use) VALUES (_proxy,TRUE,0,CURRENT_TIMESTAMP);
END IF;
END;
$$ LANGUAGE plpgsql;
""")
self.cursor.execute("END TRANSACTION;")
self.connected = True
print 'ending creating tables'
except:
print 'unable to create table'