Python psycopg2.connect() Examples

def test_copy_data_from(self):
        try:
            tmpdir = tempfile.mkdtemp()

            # create new database
            with testing.postgresql.Postgresql(base_dir=tmpdir) as pgsql:
                conn = pg8000.connect(**pgsql.dsn())
                with closing(conn.cursor()) as cursor:
                    cursor.execute("CREATE TABLE hello(id int, value varchar(256))")
                    cursor.execute("INSERT INTO hello values(1, 'hello'), (2, 'ciao')")
                conn.commit()
                conn.close()

            # create another database from first one
            data_dir = os.path.join(tmpdir, 'data')
            with testing.postgresql.Postgresql(copy_data_from=data_dir) as pgsql:
                conn = pg8000.connect(**pgsql.dsn())
                with closing(conn.cursor()) as cursor:
                    cursor.execute('SELECT * FROM hello ORDER BY id')
                    self.assertEqual(cursor.fetchall(), ([1, 'hello'], [2, 'ciao']))
                conn.close()
        finally:
            rmtree(tmpdir) 

def test_PostgresqlFactory_with_initialized_handler(self):
        def handler(pgsql):
            conn = pg8000.connect(**pgsql.dsn())
            with closing(conn.cursor()) as cursor:
                cursor.execute("CREATE TABLE hello(id int, value varchar(256))")
                cursor.execute("INSERT INTO hello values(1, 'hello'), (2, 'ciao')")
            conn.commit()
            conn.close()

        Postgresql = testing.postgresql.PostgresqlFactory(cache_initialized_db=True,
                                                          on_initialized=handler)
        try:
            with Postgresql() as pgsql:
                conn = pg8000.connect(**pgsql.dsn())
                with closing(conn.cursor()) as cursor:
                    cursor.execute('SELECT * FROM hello ORDER BY id')
                    self.assertEqual(cursor.fetchall(), ([1, 'hello'], [2, 'ciao']))
                conn.close()
        finally:
            Postgresql.clear_cache() 

def create_tables(self, request):
        self.conn = psycopg2.connect(host="127.0.0.1",
                                     user="piiuser",
                                     password="p11secret",
                                     database="piidb")

        self.conn.autocommit = True

        with self.conn.cursor() as cursor:
            self.execute_script(cursor, char_data_types)
            cursor.close()

        def drop_tables():
            with self.conn.cursor() as d_cursor:
                d_cursor.execute(self.char_db_drop)
                logging.info("Executed drop script")
                d_cursor.close()
            self.conn.close()

        request.addfinalizer(drop_tables) 

def process(iid, infile):
    dbh = psycopg2.connect(database="firmware", user="firmadyne",
                           password="firmadyne", host="127.0.0.1")
    cur = dbh.cursor()

    (files, links) = getFileHashes(infile)

    oids = getOids(files, cur)

    fdict = dict([(h, (filename, uid, gid, mode, mime, score)) \
            for (filename, h, uid, gid, mode, mime, score) in files])

    file2oid = [(fdict[h], oid) for (h, oid) in oids.items()]

    insertObjectToImage(iid, file2oid, links, cur)

    dbh.commit()

    dbh.close() 

def __init__(
        self,
        database=None,
        user=None,
        password=None,
        host=None,
        port=None,
        dsn=None,
        **kwargs,
    ):
        self._conn_params = {}
        self.conn = None
        self.dbname = None
        self.user = None
        self.password = None
        self.host = None
        self.port = None
        self.server_version = None
        self.extra_args = None
        self.connect(database, user, password, host, port, dsn, **kwargs)
        self.reset_expanded = None 

def insert_ip (iid, ip):
    import psycopg2
    db = psycopg2.connect (dbname = "firmware",
                           user = "firmadyne",
                           password = "firmadyne",
                           host = "127.0.0.1")
    try:
        cur = db.cursor()
        cur.execute("UPDATE image SET ip='" + ip + "' WHERE id=" + iid)
        db.commit()
    except BaseException:
        ret = False
        traceback.print_exc()
        db.rollback()
    finally:
        if cur:
            cur.close() 

def __init__(self, database, relation, port=None):
        """Postgres estimator (i.e., EXPLAIN).  Must have the PG server live.
        E.g.,
            def MakeEstimators():
                return [Postgres('dmv', 'vehicle_reg', None), ...]
        Args:
          database: string, the database name.
          relation: string, the relation name.
          port: int, the port.
        """
        import psycopg2

        super(Postgres, self).__init__()

        self.conn = psycopg2.connect(database=database, port=port)
        self.conn.autocommit = True
        self.cursor = self.conn.cursor()

        self.cursor.execute('analyze ' + relation + ';')
        self.conn.commit()

        self.database = database
        self.relation = relation 

def init_pg(pg_conn):
    """
    Initialize pg database
    """
    if pg_conn is None:
        print('I am unable to connect to the database')
        sys.exit(1)
    cur = pg_conn.cursor()

    sql_files = [f for f in listdir(SQL_SERVER_PATH) if isfile(join(SQL_SERVER_PATH, f))]

    for sql_file in sql_files:
        with open('%s/%s' % (SQL_SERVER_PATH, sql_file), 'r') as sql_model_file:
            cur.execute(sql_model_file.read())
            pg_conn.commit()

    cur.close()
    pg_conn.close() 

def clean_pg(pg_conn):
    """
    Clean pg database
    """
    if pg_conn is None:
        print('I am unable to connect to the database')
        sys.exit(1)
    cur = pg_conn.cursor()

    cur.execute(
        """
        DELETE FROM USERS
        """)
    cur.execute(
        """
        DELETE FROM REVOCATION
        """)
    pg_conn.commit()

    cur.close()
    pg_conn.close() 

def _connect(self):
        if self.application_name is None:
            st = inspect.stack()
            self.application_name = os.path.basename(st[-1][1])
        conn_args = dict(
            host=self.url.hostname,
            port=self.url.port,
            database=self.url.database,
            user=self.url.username,
            password=self.url.password,
            application_name=self.application_name + "/" + hgvs.__version__,
        )
        if self.pooling:
            _logger.info("Using UTA ThreadedConnectionPool")
            self._pool = psycopg2.pool.ThreadedConnectionPool(
                hgvs.global_config.uta.pool_min, hgvs.global_config.uta.pool_max, **conn_args)
        else:
            self._conn = psycopg2.connect(**conn_args)
            self._conn.autocommit = True

        self._ensure_schema_exists()

        # remap sqlite's ? placeholders to psycopg2's %s
        self._queries = {k: v.replace('?', '%s') for k, v in six.iteritems(self._queries)} 

def _connect(self, key=None):
        """Create a new connection and assign it to 'key' if not None."""
        if self._dsn:
            conn = psycopg2.connect(self._dsn)
        else:
            conn = psycopg2.connect(**self._db_config)

        if not self._disable_pooling:
            if key is not None:
                self._used[key] = conn
                self._rused[id(conn)] = key
                self._tused[id(conn)] = time.time()
            else:
                self._pool.append(conn)

        self._log('Connection created %s' % conn)
        return conn 

def licensefossology((packages)):
	## Also run FOSSology. This requires that the user has enough privileges to actually connect to the
	## FOSSology database, for example by being in the correct group.
	fossologyres = []
	fossscanfiles = map(lambda x: os.path.join(x[2], x[3]), packages)
	scanargs = ["/usr/share/fossology/nomos/agent/nomossa"] + fossscanfiles
	p2 = subprocess.Popen(scanargs, stdin=subprocess.PIPE, stdout=subprocess.PIPE)
	(stanout, stanerr) = p2.communicate()
	if "FATAL" in stanout:
		## TODO: better error handling
		return None
	else:
		fosslines = stanout.strip().split("\n")
		for j in range(0,len(fosslines)):
			fossysplit = fosslines[j].strip().rsplit(" ", 1)
			licenses = fossysplit[-1].split(',')
			fossologyres.append((packages[j][5], set(licenses)))
	return fossologyres

## TODO: get rid of ninkaversion before we call this method
## TODO: process more files at once to reduce overhead of calling ctags 

def get_connection_params(self):
        settings_dict = self.settings_dict
        # None may be used to connect to the default 'postgres' db
        if settings_dict['NAME'] == '':
            from django.core.exceptions import ImproperlyConfigured
            raise ImproperlyConfigured(
                "settings.DATABASES is improperly configured. "
                "Please supply the NAME value.")
        conn_params = {
            'database': settings_dict['NAME'] or 'postgres',
        }
        conn_params.update(settings_dict['OPTIONS'])
        conn_params.pop('isolation_level', None)
        if settings_dict['USER']:
            conn_params['user'] = settings_dict['USER']
        if settings_dict['PASSWORD']:
            conn_params['password'] = force_str(settings_dict['PASSWORD'])
        if settings_dict['HOST']:
            conn_params['host'] = settings_dict['HOST']
        if settings_dict['PORT']:
            conn_params['port'] = settings_dict['PORT']
        return conn_params 

def get_new_connection(self, conn_params):
        connection = Database.connect(**conn_params)

        # self.isolation_level must be set:
        # - after connecting to the database in order to obtain the database's
        #   default when no value is explicitly specified in options.
        # - before calling _set_autocommit() because if autocommit is on, that
        #   will set connection.isolation_level to ISOLATION_LEVEL_AUTOCOMMIT.
        options = self.settings_dict['OPTIONS']
        try:
            self.isolation_level = options['isolation_level']
        except KeyError:
            self.isolation_level = connection.isolation_level
        else:
            # Set the isolation level to the value from OPTIONS.
            if self.isolation_level != connection.isolation_level:
                connection.set_session(isolation_level=self.isolation_level)

        return connection 

def send_command(command, timeout=10):
    _, port = MULTICAST.get('mihome')
    if isinstance(command.get('data'), dict):
        command['data'] = json.dumps(command['data'])
    address = get_store().get('gateway_addr')
    if address is None:
        print("Didn't receive any heartbeat from gateway yet. Delaying request for 10 seconds.")
        time.sleep(10)
    sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    sock.settimeout(timeout)
    sock.connect((address, port))
    sock.send(json.dumps(command).encode('ascii'))
    data = None
    try:
        data, addr = sock.recvfrom(SOCKET_BUFSIZE)
    except ConnectionRefusedError:
        print("send_command :: recvfrom() connection refused: {}:{}".format(address.decode(), port))
    except socket.timeout:
        print("send_command :: recvfrom() timed out: {}:{}".format(address.decode(), port))
    finally:
        sock.close()
    return data 

def connect_to_db(connection_config: DatabaseConnectionConfig):
    """
    Return a connection to the mycroft database for the specified user.

    Use this function when connecting to a database in an application that
    does not benefit from connection pooling (e.g. a batch script or a
    python notebook)

    :param connection_config: data needed to establish a connection
    :return: database connection
    """
    log_msg = 'establishing connection to the {db_name} database'
    _log.info(log_msg.format(db_name=connection_config.db_name))
    db = connect(
        host=connection_config.host,
        dbname=connection_config.db_name,
        user=connection_config.user,
        password=connection_config.password,
        port=connection_config.port,
        cursor_factory=connection_config.cursor_factory,
        sslmode=connection_config.sslmode
    )
    db.autocommit = connection_config.autocommit

    return db 

def create_tables(self, request):
        self.conn = psycopg2.connect(host="127.0.0.1",
                                     user="piiuser",
                                     password="p11secret",
                                     database="piidb")

        self.conn.autocommit = True

        with self.conn.cursor() as cursor:
            self.execute_script(cursor, pii_data_script)
            self.execute_script(cursor, self.second_schema)
            cursor.close()

        def drop_tables():
            with self.conn.cursor() as d_cursor:
                d_cursor.execute(self.pii_db_drop)
                logging.info("Executed drop script")
                d_cursor.close()
            self.conn.close()

        request.addfinalizer(drop_tables) 

def __init__(self, db_name, user=None):
        db_host = environ.get('DB_HOST', '127.0.0.1')
        db_port = environ.get('DB_PORT', 5432)
        db_ssl_mode = environ.get('DB_SSLMODE')
        if db_name in ('postgres', 'defaultdb', 'mycroft_template'):
            db_user = environ.get('POSTGRES_USER', 'postgres')
            db_password = environ.get('POSTGRES_PASSWORD')
        else:
            db_user = environ.get('DB_USER', 'selene')
            db_password = environ['DB_PASSWORD']

        if user is not None:
            db_user = user

        self.db = connect(
            dbname=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port,
            sslmode=db_ssl_mode
        )
        self.db.autocommit = True 

def create_tables(self, request):
        self.conn = pymysql.connect(host="127.0.0.1",
                                    user="piiuser",
                                    password="p11secret",
                                    database="piidb"
                                    )

        with self.conn.cursor() as cursor:
            self.execute_script(cursor, char_data_types)
            cursor.execute("commit")
            cursor.close()

        def drop_tables():
            with self.conn.cursor() as drop_cursor:
                self.execute_script(drop_cursor, self.char_db_drop)
                logging.info("Executed drop script")
                drop_cursor.close()
            self.conn.close()

        request.addfinalizer(drop_tables) 

def update(self):
        try:
            self.connect()
            if self.allow_update:
                self.update_password()
            else:
                self.fail('Only the password of %s can be updated' % self.user)
        except Exception as e:
            self.fail('Failed to update the user, %s' % e)
        finally:
            self.close() 

def __init__(self, db, user, password):
        try:
            self._db_con = psycopg2.connect(dbname=db, user=user,
                                            password=password)
            self._db_cur = self._db_con.cursor()
        except Exception as e:
            print e 

def create(self):
        try:
            self.connect()
            self.create_user()
            self.physical_resource_id = self.url
        except Exception as e:
            self.physical_resource_id = 'could-not-create'
            self.fail('Failed to create user, %s' % e)
        finally:
            self.close() 

def open_database_connection(self):

		self.db_interface = psycopg2.connect(
				database = settings.DATABASE_DB_NAME,
				user     = settings.DATABASE_USER,
				password = settings.DATABASE_PASS,
				host     = settings.DATABASE_IP,
			) 

def connect(self):
        log.info('connecting to database %s on port %d as user %s', self.host, self.port, self.dbowner)
        try:
            self.connection = psycopg2.connect(**self.connect_info)
            self.connection.set_session(autocommit=True)
        except Exception as e:
            raise ValueError('Failed to connect, %s' % e) 

def _create_raw_connection(conn_string, logger=LOG):
    conn = psycopg2.connect(conn_string, connection_factory=LoggingConnection)
    conn.initialize(logger)

    return conn 

def step_impl(context):
    context.conn = None
    conn = psycopg2.connect('dbname=postgres')
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute("select pg_terminate_backend(pid) " +
                "from pg_stat_activity where datname='pgmigratetest'")
    cur.execute('drop database if exists pgmigratetest')
    cur.execute('create database pgmigratetest')
    context.conn = psycopg2.connect('dbname=pgmigratetest') 

def _get_db_conn(self):
        if self.db and self.db.closed != 0:
            self.db.close()
            self.db = None

        if not self.db:
            # get_database config may return None or empty
            db_config = database.get_config()
            if not db_config or "DatabaseType" not in db_config:
                raise ValueError(
                    "Database not set as VCAP or DATABASE_URL. Check "
                    "documentation to see supported configuration options."
                )
            if db_config["DatabaseType"] != "PostgreSQL":
                raise Exception(
                    "Metrics only supports postgresql, not %s"
                    % db_config["DatabaseType"]
                )

            host, port = self._get_db_host_and_port(db_config["DatabaseHost"])
            self.db = psycopg2.connect(
                "options='-c statement_timeout=60s'",
                database=db_config["DatabaseName"],
                user=db_config["DatabaseUserName"],
                password=db_config["DatabasePassword"],
                host=host,
                port=port,
                connect_timeout=3,
            )
            self.db.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT
            )
        return self.db 

def _get_database_tcp_latency(self, timeout: float = 5):
        db_config = database.get_config()
        host, port = self._get_db_host_and_port(db_config["DatabaseHost"])
        # New Socket and Time out
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.settimeout(timeout)

        # Start a timer
        s_start = timer()

        # Try to Connect
        try:
            sock.connect((host, int(port)))
            sock.shutdown(socket.SHUT_RD)
            sock.close()

        # If something bad happens, the latency is None
        except socket.timeout:
            return None
        except OSError:
            return None

        # Stop Timer
        s_stop = timer()
        s_runtime = "%.2f" % (1000 * (s_stop - s_start))

        return s_runtime 

def _connect():
    try:
        conn = psycopg2.connect(
            database=conf.postgresql_db_name,
            user=conf.postgresql_username,
            password=conf.postgresql_password,
            host=conf.postgresql_host,
            port=str(POSTGRESQL_DEFAULT_PORT)
        )
        conn.autocommit = True
        return conn
    except psycopg2.DatabaseError as e:
        raise Exception('Error during connection to postgres: {0}'
                        .format(str(e))) 

def get_connection(cls):
        return psycopg2.connect(host="127.0.0.1",
                                user="piiuser",
                                password="p11secret",
                                database="piidb"
                                )