Python psycopg2.connect函数代码示例

def main():
        
    # Load connection params from the configuration file
    config = ConfigParser.ConfigParser()
    config.read(['consumer.conf', 'local_consumer.conf'])
    dbhost = config.get('Database', 'dbhost')
    dbname = config.get('Database', 'dbname')
    dbuser = config.get('Database', 'dbuser')
    dbpass = config.get('Database', 'dbpass')
    dbport = config.get('Database', 'dbport')
    redisdb = config.get('Redis', 'redishost')
    TERM_OUT = config.get('Consumer', 'term_out')
    
    # Handle DBs without password
    if not dbpass:
        # Connect without password
        dbcon = psycopg2.connect("host="+dbhost+" user="+dbuser+" dbname="+dbname+" port="+dbport)
    else:
        dbcon = psycopg2.connect("host="+dbhost+" user="+dbuser+" password="+dbpass+" dbname="+dbname+" port="+dbport)
    
    dbcon.autocommit = True

    curs = dbcon.cursor()
    
    sql = "INSERT INTO market_data_emdrstats (status_type, status_count, message_timestamp) SELECT status_type, count(id), date_trunc('minute',now()) FROM market_data_emdrstatsworking GROUP BY status_type"
    curs.execute(sql)
    
    sql = "TRUNCATE market_data_emdrstatsworking"
    curs.execute(sql)

    def check(self, instance):
        host = instance.get('host', '')
        port = instance.get('port', '')
        user = instance.get('username', '')
        passwd = instance.get('password', '')
        tags = instance.get('tags', [])
        key = '%s:%s' % (host, port)

        if key in self.dbs:
            db = self.dbs[key]
        elif host != '' and user != '':
            try:
                import psycopg2 as pg
                if host == 'localhost' and passwd == '':
                    # Use ident method
                    db = pg.connect("user=%s dbname=postgres" % user)
                elif port != '':
                    db = pg.connect(host=host, port=port, user=user,
                        password=passwd, database='postgres')
                else:
                    db = pg.connect(host=host, user=user, password=passwd,
                        database='postgres')
            except ImportError, e:
                self.log.exception("Cannot import psypg2")
                return
            except Exception, e: #Fixme: catch only pg errors
                self.log.exception('PostgreSql connection error')
                return

def main_page():
    if request.method == 'POST':

        if request.form['action'] == 'add':
            newRst = request.form['newRst']
            username = current_user.userName
            with dbapi2.connect(flask.current_app.config['dsn']) as connection:
                cursor = connection.cursor()

                query = """INSERT INTO RESTAURANT(NAME, USERNAME) VALUES(%s, %s)"""
                cursor.execute(query,(newRst, username))
                connection.commit()
            with dbapi2.connect(flask.current_app.config['dsn']) as connection:
                cursor = connection.cursor()

                query = """INSERT INTO RST_DETAILS (NAME,LOCATION,CATEGORY)
                VALUES ('%s', '%s', '%s') """ %(newRst,'not provided yet.','not provided yet.')
                cursor.execute(query)
                connection.commit()


            return redirect(url_for('site.main_page'))
    if request.method == 'GET':
        with dbapi2.connect(flask.current_app.config['dsn']) as connection:
            cursor = connection.cursor()
        query = """SELECT * FROM RESTAURANT"""
        cursor.execute(query)
        names=cursor.fetchall()
        query = """SELECT * FROM USERS"""
        cursor.execute(query)
        users=cursor.fetchall()
        connection.commit()
        return render_template('mainpage.html',names=names,users=users,user=current_user)

def child(ckt,tshash):
    cur=conn=None
    try:
      conn=psycopg2.connect("dbname='timeseries' user='postgres' host='localhost' password='postgres'")
      cur=conn.cursor()
      print "obtained psycopg2 connection and cursor for circuit: "+str(ckt)

      for (ts,value) in tshash.items():
         print "inserting (timestamp, watts)"+str(ts)+str(value)+"...."
         try: 
            cur.execute("insert into watts_"+str(Global.logdir)+"_"+str(ckt)+" values(%s,%s)",(ts,value,))
            conn.commit()
         except Exception,e:
            conn.rollback()
            if cur is not None:
              cur.close()
            if conn is not None:
              conn.close()
            conn=psycopg2.connect("dbname='timeseries' user='postgres' host='localhost' password='postgres'")
            cur=conn.cursor()
            print e 

    except Exception,e:
       print "user_add DATABASE EXCEPTION!!!!"

       print e
       conn.rollback()

def get_currents():
    ''' Return dict of current values '''
    dbconn = psycopg2.connect(database='iem', host='iemdb', user='nobody')
    cursor = dbconn.cursor()
    dbconn2 = psycopg2.connect(database='isuag', host='iemdb', user='nobody')
    cursor2 = dbconn2.cursor()
    data = {}
    cursor.execute("""
    SELECT id, valid, tmpf, relh from current c JOIN stations t on
    (t.iemid = c.iemid) WHERE valid > now() - '3 hours'::interval and
    t.network = 'ISUSM'
    """)
    valid = None
    for row in cursor:
        data[row[0]] = {'tmpf': row[2],
                        'rh': row[3],
                        'valid': row[1],
                        'high': None}
        if valid is None:
            valid = row[1]

    # Go get daily values
    cursor2.execute("""SELECT station, tair_c_max from sm_daily
    where valid = %s
    """, (valid,))
    for row in cursor2:
        data[row[0]]['high'] = temperature(row[1], 'C').value('F')

    cursor.close()
    dbconn.close()
    return data

def addtournament():
    if request.method == 'POST':
        with dbapi2.connect(app.config['dsn']) as connection:
            cursor = connection.cursor()

            Name = request.form['Name']
            City_ID = request.form['selectedValue']

            query = """CREATE TABLE IF NOT EXISTS Tournament ( Tournament_ID SERIAL PRIMARY KEY NOT NULL, Tournament_Name CHAR(50) NOT NULL, Tournament_CityID INT REFERENCES City (City_ID) ON DELETE CASCADE ON UPDATE CASCADE);"""
            cursor.execute(query)
            try:
                queryWithFormat = """INSERT INTO Tournament (Tournament_Name, Tournament_CityID ) VALUES (%s, %s)"""
                cursor.execute(queryWithFormat, (Name, City_ID))
            except dbapi2.DatabaseError:
                connection.rollback()
                return "error happened"
        return redirect(url_for('tournamentlist'))
    with dbapi2.connect(app.config['dsn']) as connection:
        cursor = connection.cursor()
        statement = """SELECT City_ID, City_Name FROM City ORDER BY City_ID"""
        cursor.execute(statement)
        cities=[]
        for City_ID,City_Name in cursor:
            city=(City(City_ID,City_Name))
            cities.append(city)
    return render_template('addtournament.html', Cities = cities)

def addaccommodation():
    if session['isValid'] == False:
        return "You are not authorized"
    if request.method == 'POST':
        with dbapi2.connect(app.config['dsn']) as connection:
            cursor = connection.cursor()

            Name = request.form['Name']
            AccommodationID = request.form['selectedValue']


            query = """CREATE TABLE IF NOT EXISTS Accommodation ( Accommodation_ID SERIAL PRIMARY KEY NOT NULL, Accommodation_Name CHAR(50) NOT NULL, Accommodation_CityID INT REFERENCES City (City_ID) ON DELETE CASCADE ON UPDATE CASCADE    );"""
            cursor.execute(query)
            try:
                queryWithFormat = """INSERT INTO Accommodation (Accommodation_Name, Accommodation_CityID) VALUES (%s, %s)"""
                cursor.execute(queryWithFormat, (Name, AccommodationID))
            except dbapi2.DatabaseError:
                connection.rollback()
                return "error happened"
        return redirect(url_for('accommodationlist'))
    with dbapi2.connect(app.config['dsn']) as connection:
        cursor = connection.cursor()
        retval = ""
        statement = """SELECT City_ID, City_Name FROM City ORDER BY City_ID"""
        cursor.execute(statement)
        cities=[]
        for City_ID,City_Name in cursor:
           city=(City(City_ID,City_Name))
           cities.append(city)
    return render_template('addaccommodation.html', Cities = cities)

def connect():
	BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))


	# local
	if 'WTFPrinceton' in BASE_DIR:
		conn = psycopg2.connect(
	    database='d8qajk44a19ere',
	    user='',
	    password='',
	    host='localhost',
	    port='',
		)
		return conn
	else: # heroku
		urlparse.uses_netloc.append("postgres")
		url = urlparse.urlparse(os.environ["DATABASE_URL"])
		conn = psycopg2.connect(
		    database=url.path[1:],
		    user=url.username,
		    password=url.password,
		    host=url.hostname,
		    port=url.port,
		)
		return conn

	def connect(self):


		if all([
			hasattr(self, '_psqlDbIpAddr'),
			hasattr(self, '_psqlDbName'),
			hasattr(self, '_psqlUserName'),
			hasattr(self, '_psqlUserPass'),
			]):
			pass
		else:
			try:
				# hook up login creds (overridden for tests)
				self._psqlDbIpAddr = settings.PSQL_IP
				self._psqlDbName   = settings.PSQL_DB_NAME
				self._psqlUserName = settings.PSQL_USER
				self._psqlUserPass = settings.PSQL_PASS
			except:
				print("WARNING: DB Credentials not available. Is this a test environment?")

		try:
			self.conn = psycopg2.connect(dbname  = self._psqlDbName,
										user     = self._psqlUserName,
										password = self._psqlUserPass)

		except psycopg2.OperationalError:
			self.conn = psycopg2.connect(host    = self._psqlDbIpAddr,
										dbname   = self._psqlDbName,
										user     = self._psqlUserName,
										password = self._psqlUserPass)

def race_join_page(key=None):
    if 'username' in session:
        name = session['username']
        with dbapi2.connect(app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("SELECT memberid FROM MEMBERS WHERE username='%s';"%name)
            id = cursor.fetchone()
            connection.commit()
        with dbapi2.connect(app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = ("INSERT INTO RACE_RESULTS (MEMBERID, RACEID ) VALUES (%s, %s)")
            cursor.execute(query, (id, key))
            connection.commit()
        with dbapi2.connect(app.config['dsn']) as connection:
            cursor = connection.cursor()
            cursor.execute("SELECT participant_count FROM RACE WHERE id='%s';"%key)
            participant_count = cursor.fetchone()[0]
            participant_count = participant_count + 1
            connection.commit()
        with dbapi2.connect(app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = ("UPDATE RACE SET participant_count=%s  WHERE (id=%s)")
            cursor.execute(query, (participant_count, key))
            connection.commit()
        now = datetime.datetime.now()
        return redirect(url_for('race_page', key=key))
    else:
        race = app.store.get_race(key) if key is not None else None
        now = datetime.datetime.now()
        return redirect(url_for('race_page', key=key))

    def test_success(self):
        # Case to test for a successful tranformation of a module from
        #   cnxml to html.
        ident, filename = 2, 'index.cnxml'  # m42955

        with psycopg2.connect(self.connection_string) as db_connection:
            with db_connection.cursor() as cursor:
                # delete module_ident 2 index.cnxml.html
                cursor.execute("DELETE FROM module_files WHERE module_ident = 2 "
                               "AND filename = 'index.cnxml.html'")
        self.call_target(ident)

        with psycopg2.connect(self.connection_string) as db_connection:
            with db_connection.cursor() as cursor:
                cursor.execute("SELECT file FROM files "
                               "  WHERE fileid = "
                               "    (SELECT fileid FROM module_files "
                               "       WHERE module_ident = %s "
                               "         AND filename = 'index.cnxml.html');",
                               (ident,))
                index_html = cursor.fetchone()[0][:]
        # We only need to test that the file got transformed and placed
        #   placed in the database, the transform itself should be verified.
        #   independent of this code.
        self.assertTrue(index_html.find('<html') >= 0)

def cable_schools():
	for myState in ["AK","AL","AS","AZ","AR","CA","CO","CT","DC","DE","FL","GA","GU","HI","ID","IL",
		"IN","IA","KS","KY","LA","ME","MD","MA","MI","MN","MS","MO","MP","MT","NE","NV","NH",
		"NJ","NM","NY","NC","ND","OH","OK","OR","PA","PR","RI","SC","TN","TX","UT","VA",
		"VI","VT","WA","WI","WV","WY"]:
		myConn = "dbname=" + db + " host=" + myHost + " port=" + myPort + " user=" + myUser
		conn = psycopg2.connect(myConn)
		ctest = conn.cursor()
		for shp in ["block","road","address"]:
			ctest.execute("select * from information_schema.tables where table_name=%s", ("private_school_transtech_" + myState.lower() + "_" + shp,))
			if bool(ctest.rowcount):
				print myState
				print shp
				myConn = "dbname=" + db + " host=" + myHost + " port=" + myPort + " user=" + myUser
				conn = psycopg2.connect(myConn)
				c = conn.cursor()
				dropSQL = "DROP TABLE IF EXISTS " + schema + ".private_school_unique_cable_" + myState + "_" + shp + "; "
				c.execute(dropSQL)
				theSQL = "CREATE TABLE " + schema + ".private_school_unique_cable_" + myState + "_" + shp + " AS "
				theSQL = theSQL + "SELECT school_id, count(*) FROM " + schema + ".private_school_transtech_"
				theSQL = theSQL + myState + "_" + shp + " WHERE transtech = '40' or transtech = '41'"
				theSQL = theSQL + " GROUP BY school_id ORDER BY count desc;"
				print theSQL
				c.execute(theSQL)
				conn.commit()
				c.close()
		ctest.close()

    def create_database(self):

        try:
            with psycopg2.connect(
                    host=self.host,
                    port=self.port,
                    database="postgres",
                    user=self.user,
                    password=self.password) as conn:
                conn.autocommit = True
                with conn.cursor() as curs:
                    curs.execute("CREATE DATABASE {}".format("test"))
        except:
            pass

        self.conn = psycopg2.connect(
                host=self.host,
                port=self.port,
                database="test",
                user=self.user,
                password=self.password)
        self.conn.autocommit = True
        self.curs = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

        self.curs.execute(create_table_command.format(self.db_name))

    def test_blocking(self):
        statement = """
            UPDATE pgbench_branches
            SET bbalance = bbalance + 10
            WHERE bid = 1
        """

        blocking_conn = psycopg2.connect(
            database=self.dbname,
            cursor_factory=psycopg2.extras.NamedTupleCursor
        )

        blocking_cursor = blocking_conn.cursor()
        blocking_cursor.execute(statement)

        async_conn = psycopg2.connect(
            database=self.dbname,
            cursor_factory=psycopg2.extras.NamedTupleCursor,
            async=1
        )

        psycopg2.extras.wait_select(async_conn)
        async_cursor = async_conn.cursor()
        async_cursor.execute(statement)

        with PgExtras(dsn=self.dsn) as pg:
            results = pg.blocking()

        self.assertEqual(len(results), 1)

    def get_connection(self, key, host, port, user, password, dbname, use_cached=True):
        "Get and memoize connections to instances"
        if key in self.dbs and use_cached:
            return self.dbs[key]

        elif host != "" and user != "":
            try:
                import psycopg2 as pg
            except ImportError:
                raise ImportError("psycopg2 library cannot be imported. Please check the installation instruction on the Datadog Website.")
            
            if host == 'localhost' and password == '':
                # Use ident method
                connection = pg.connect("user=%s dbname=%s" % (user, dbname))
            elif port != '':
                connection = pg.connect(host=host, port=port, user=user,
                    password=password, database=dbname)
            else:
                connection = pg.connect(host=host, user=user, password=password,
                    database=dbname)
        else:
            if not host:
                raise CheckException("Please specify a Postgres host to connect to.")
            elif not user:
                raise CheckException("Please specify a user to connect to Postgres as.")

        try:
            connection.autocommit = True
        except AttributeError:
            # connection.autocommit was added in version 2.4.2
            from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
            connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        
        self.dbs[key] = connection
        return connection