Python

Psycopg is a Python API used to execute SQL statements and provides a unified access API for PostgreSQL and GaussDB. Applications can perform data operations based on psycopg. Psycopg2 encapsulates libpq. Some code is implemented using the C language, which is efficient and secure. It provides client-side and server-side cursors, asynchronous communication and notification, and the COPY TO and COPY FROM functions. Psycopg2 supports multiple types of Python out-of-the-box and adapts to PostgreSQL data types. Through the flexible object adaptation system, you can extend and customize APIs. Psycopg2 is compatible with Unicode and Python 3.

openGauss supports the psycopg2 feature and allows psycopg2 to be connected in SSL mode.

Table 1 Platforms supported by Psycopg

OS	Platform
EulerOS 2.5	x86_64
EulerOS 2.8	ARM64

Loading a Driver

Before using the driver, perform the following operations:
1. Download the compiled psycopg2 package from the openGauss website.
  NOTE: The psycopg2 downloaded from the openGauss website matches Python 3.6. If you use Python of another version, you need to compile psycopg2. The compilation method is similar to that in the PostgreSQL database. You only need to modify the code for verifying the version number of setup.py during compilation by running the following command:
```
sed -i "s/(pgmajor, pgminor, pgpatch)/(9, 2, 4)/g" setup.py
```
2. Decompress the driver package of the corresponding version and copy psycopg2 to the third-party package folder (that is, the site-packages directory) in the Python installation directory.
3. Ensure that the permission on the psycopg2 directory is at least 755. Otherwise, the file cannot be accessed due to permission issues.
4. For a non-database user, set LD_LIBRARY_PATH to the lib directory generated after decompression.
Load a database driver before creating a database connection:
```
import  psycopg2
```

Connecting to a Database

The following Python code shows how to connect to an existing database. If the database does not exist, it will be automatically created and a database object will be returned.

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database="testdb", user="openGauss", password="xxxxxxxx", host="127.0.0.1", port="26000")

In the preceding code, replace the content in bold based on the actual requirements. Specify testdb as the database name.

Creating a Table

The following Python program will be used to create a table in the previously created database testdb:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user="openGauss", password="xxxxxxxx", host="127.0.0.1", port="26000")

cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')

conn.commit()
conn.close()

When the preceding program is executed, the COMPANY table is created in the testdb database.

Insert Operation

The following Python program shows how to create a record in the COMPANY table created in the preceding example:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user="openGauss", password="xxxxxxxx", host="127.0.0.1", port="26000")

cur = conn.cursor()

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
conn.close()

When the above program is executed, it will create/insert the given record in the COMPANY table.

SELECT Operation

The following Python program shows how to obtain and display records from the COMPANY table created in the preceding example:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user="openGauss", password="xxxxxxxx", host="127.0.0.1", port="26000")

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3])

conn.close()

When the preceding program is executed, the following information is displayed:

ID =,  1
NAME =,  Paul
ADDRESS =,  California
SALARY =,  20000.0

ID =,  2
NAME =,  Allen
ADDRESS =,  Texas
SALARY =,  15000.0

ID =,  3
NAME =,  Teddy
ADDRESS =,  Norway
SALARY =,  20000.0

ID =,  4
NAME =,  Mark
ADDRESS =,  Rich-Mond
SALARY =,  65000.0

Update Operation

The following Python code shows how to use the UPDATE statement to update any record, and then obtain and display the updated record from the COMPANY table:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user="openGauss", password="xxxxxxxx", host="127.0.0.1", port="26000")

cur = conn.cursor()

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3])

conn.close()

When the preceding program is executed, the following results are generated:

ID =,  1
NAME =,  Paul
ADDRESS =,  California
SALARY =,  25000.0

ID =,  2
NAME =,  Allen
ADDRESS =,  Texas
SALARY =,  15000.0

ID =,  3
NAME =,  Teddy
ADDRESS =,  Norway
SALARY =,  20000.0

ID =,  4
NAME =,  Mark
ADDRESS =,  Rich-Mond
SALARY =,  65000.0

Delete Operation

The following Python code shows how to use the DELETE statement to delete records, and then obtain and display the remaining records from the COMPANY table:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user="openGauss", password="xxxxxxxx", host="127.0.0.1", port="26000")

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")
conn.commit

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3])

conn.close()

When the preceding program is executed, the following results are generated:

ID =,  1
NAME =,  Paul
ADDRESS =,  California
SALARY =,  20000.0

ID =,  3
NAME =,  Teddy
ADDRESS =,  Norway
SALARY =,  20000.0

ID =,  4
NAME =,  Mark
ADDRESS =,  Rich-Mond
SALARY =,  65000.0