Python getConnection Examples

Example #1

def deleteversion(versionId):
    conn = getConnection()
    cur = conn.cursor()
    print 'Deleting ' + versionId
    print 'Delete journeys'
    cur.execute(
        """
DELETE FROM journey WHERE availabilityconditionref IN
 (SELECT DISTINCT ac.id FROM availabilitycondition as ac LEFT JOIN version ON (version.id = versionref) WHERE version.operator_id = %s)""",
        [versionId])
    print 'Delete validdays'
    cur.execute(
        """
DELETE FROM availabilityconditionday WHERE availabilityconditionref IN                    
 (SELECT DISTINCT ac.id FROM availabilitycondition as ac LEFT JOIN version ON (version.id = versionref) WHERE version.operator_id = %s)""",
        [versionId])
    print 'Delete availabilityconditions'
    cur.execute(
        """
DELETE FROM availabilitycondition WHERE id IN
 (SELECT DISTINCT ac.id FROM availabilitycondition as ac LEFT JOIN version ON (version.id = versionref) WHERE version.operator_id = %s)""",
        [versionId])
    print 'Delete versionrecord'
    cur.execute("""DELETE FROM version WHERE operator_id = %s""", [versionId])
    conn.commit()
    cur.close()
    conn.close()

Example #2

File: gvb.py Project: bliksemlabs/bliksemintegration

def merge():
    conn = getConnection()
    cur = conn.cursor()
    cur.execute("""
begin;
UPDATE availabilityconditionday set isavailable = false WHERE availabilityconditionref IN
(SELECT availabilitycondition.id FROM availabilitycondition
                                 JOIN version ON (versionref = version.id) 
                                 JOIN datasource ON (datasourceref= datasource.id)
 WHERE datasource.operator_id = 'GVB');

UPDATE availabilityconditionday set isavailable = true WHERE availabilityconditionref||':'||validdate in (
SELECT DISTINCT ON (unitcode,ac.validdate) ac.id||':'||ac.validdate
FROM 
 (SELECT *,generate_series(fromdate,todate,interval '1 day')::date as validdate FROM availabilitycondition) as ac JOIN 
 (SELECT version.id as versionref,version.operator_id,startdate,enddate,version.description,row_number() over (order by startdate ASC,enddate DESC) 
as idx
  FROM VERSION JOIN datasource ON (datasourceref= datasource.id)
  WHERE datasource.operator_id = 'GVB'
  ORDER BY startdate ASC,enddate DESC) as importorder USING (versionref)
  LEFT JOIN availabilityconditionday as ad ON (ad.availabilityconditionref = ac.id AND ad.validdate = ac.validdate)
ORDER BY unitcode ASC,ac.validdate ASC,importorder.idx DESC,(ad.validdate is not null) DESC
);

commit;
""")
    conn.commit()
    conn.close()

Example #3

def merge():
    conn = getConnection()
    cur = conn.cursor()
    cur.execute("""
begin;
UPDATE availabilityconditionday set isavailable = false WHERE availabilityconditionref IN
(SELECT availabilitycondition.id FROM availabilitycondition
                                 JOIN version ON (versionref = version.id) 
                                 JOIN datasource ON (datasourceref= datasource.id)
 WHERE datasource.operator_id = 'GVB');

UPDATE availabilityconditionday set isavailable = true WHERE availabilityconditionref||':'||validdate in (
SELECT DISTINCT ON (unitcode,ac.validdate) ac.id||':'||ac.validdate
FROM 
 (SELECT *,generate_series(fromdate,todate,interval '1 day')::date as validdate FROM availabilitycondition) as ac JOIN 
 (SELECT version.id as versionref,version.operator_id,startdate,enddate,version.description,row_number() over (order by startdate ASC,enddate DESC) 
as idx
  FROM VERSION JOIN datasource ON (datasourceref= datasource.id)
  WHERE datasource.operator_id = 'GVB'
  ORDER BY startdate ASC,enddate DESC) as importorder USING (versionref)
  LEFT JOIN availabilityconditionday as ad ON (ad.availabilityconditionref = ac.id AND ad.validdate = ac.validdate)
ORDER BY unitcode ASC,ac.validdate ASC,importorder.idx DESC,(ad.validdate is not null) DESC
);

commit;
""")
    conn.commit()
    conn.close()

Example #4

File: gvb.py Project: bliksemlabs/bliksemintegration

def deleteversion(versionId):
    conn = getConnection()
    cur = conn.cursor()
    print 'Deleting '+versionId
    print 'Delete journeys'
    cur.execute("""
DELETE FROM journey WHERE availabilityconditionref IN
 (SELECT DISTINCT ac.id FROM availabilitycondition as ac LEFT JOIN version ON (version.id = versionref) WHERE version.operator_id = %s)""",[versionId])
    print 'Delete validdays'
    cur.execute("""
DELETE FROM availabilityconditionday WHERE availabilityconditionref IN                    
 (SELECT DISTINCT ac.id FROM availabilitycondition as ac LEFT JOIN version ON (version.id = versionref) WHERE version.operator_id = %s)""",[versionId])
    print 'Delete availabilityconditions'
    cur.execute("""
DELETE FROM availabilitycondition WHERE id IN
 (SELECT DISTINCT ac.id FROM availabilitycondition as ac LEFT JOIN version ON (version.id = versionref) WHERE version.operator_id = %s)""",[versionId])
    print 'Delete versionrecord'
    cur.execute("""DELETE FROM version WHERE operator_id = %s""",[versionId])
    conn.commit()
    cur.close()
    conn.close()