Store a PgxFrame in Database

You can store a PgxFrame output to relational database tables.

The outputs of the property graph machine learning algorithms are PgxFrame(s) and this data structure can be stored in the database. The columns and rows of the PgxFrame correspond to the columns and rows of the database table.

The following example converts a PGQL result set to a PgxFrame, which is then stored as a table to the database.

• %java-pgx
• %python-pgx

var g = session.readGraphByName("BANK_GRAPH", GraphSource.PG_VIEW)
var query = "SELECT s.acct_id FROM MATCH (s) LIMIT 10"
var rs = g.queryPgql(query)
if (rs != null) {
  rs.toFrame().write().db()
    .tablename("accounts") // name of the DB table
    .overwrite(true)
    .store();
}

g = session.read_graph_by_name("BANK_GRAPH", "pg_view")
query = "SELECT s.acct_id FROM MATCH (s)"
rs = g.execute_pgql(query)
if (rs != None):
    rs.to_frame().write().db().table_name("accounts").overwrite(True).store()

On executing the notebook paragraph, the PgxFrame data gets inserted in the appropriate database table. You can verify this by viewing and querying the database table using Database Actions. See SQL Page in Database Actions for more information on running SQL statements in Database Actions.

Also, note the following:

• The generated table name and column names are case-sensitive. The preceding code example creates a database table having a lowercase name "accounts" with a column named "acct_id".

  If however, the query is:

  "SELECT s.acct_id as ACCT_ID FROM MATCH (s) limit 10"

  and table name is specified as tablename("ACCOUNTS"), then the database table will have a uppercase name "ACCOUNTS" with a column named "ACCT_ID".

• If a database table with the same name is already existing, then you can use the overwrite mode by setting overwrite(true) as seen in the preceding example. The previous table gets truncated and the data is then inserted. By default, the value is set to false.

• If you are using an Always Free Autonomous AI Database instance (that is, one with only 1 OCPU and 20GB of storage), then you must also specify that only one connection must be used when writing the PgxFrame to the table in a Java (PGX) notebook paragraph. For example, you must invoke write() as shown:

  rs.toFrame().write().db().connections(1).tablename("accounts").overwrite(true).store();