PostgreSQL

If you've come this far, we believe that you already know how to create a database in our CLI app. If not, please check Databases section and create one for further training.

Preparation

For PostgreSQL, our wrapper is enhanced with useful commands to interact with the DB. Firstly, we create the connection to the DB with:

the name of the running psql instance
the token generated for that instance

If you don't have any running instances of PostgreSQL, create one and then copy the name and app token.
The app token can be found by using:

cgc db list -d

It won't work from Jupyter, unless you provided api keys. Use your personal terminal where you performed the cgc registration.

Usage

Create a connection

import cgc.sdk as cgc
postgresql = cgc.postgresql_client("postgresql","fec387f993934ccebe4d24ef2f079720")

Create table

sql_command = sql.SQL('''
CREATE TABLE Customer
(CustomerID INT PRIMARY KEY NOT NULL,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age INT,
Phone INT
);
''')
cursor.execute(sql_command)

Insert documents

Now we can insert data to previously created table with the insert_one function

sql_command = sql.SQL('''
INSERT INTO Customer
(CustomerID,
CustomerName,
LastName,
Country,
Age,
Phone) VALUES (%s,%s,%s,%s,%s,%s)
''')
record_to_insert = (3, 'Zbigniew', "Polak", "Bali", 69, 991991991)
cursor.execute(sql_command,record_to_insert)
postgresql_client.commit()

Getting data from the db

To get data from our db, simply run the select command to retrieve the psql cursor with the data and then print out records

cursor = postgresql_client.cursor()
cursor.execute("SELECT * FROM Customer")
cursor.fetchall()

Alternatives

If you are familiar with psycopg2, you can use it to access the database without the need for cgc sdk. Use

import psycopg2

client = psycopg2.connect(
                    database="db",
                    host="postgresql",
                    user="admin",
                    password="fec387f993934ccebe4d24ef2f079720",
                )
cursor = client.cursor()

cursor.execute("SELECT * FROM Customer")
cursor.fetchall()

PostgreSQL

Preparation​

Usage​

Create a connection​

Create table​

Insert documents​

Getting data from the db​

Alternatives​