PostgreSQL Basic Tutorial

PostgreSQL Advanced Tutorial

PostgreSQL Interface

PostgreSQL Python Interface

installation

PostgreSQL can use psycopg2module integrates with Python. psycopg2is a PostgreSQL database adapter for the Python programming language. psycopg2The purpose of the writing is very small, fast, and stable as a cornerstone. You do not need to install this module separately, because by default, it is provided with Python2.5.

.x version provided together.

If it is not installed on your machine, you can use the yum command to install it in the following way-psycopg2

$yum install python2To use psycopg

module, it must first create a Connection object representing the database, and then you can choose to create a cursor object, which will help to execute all SQL statements.2 Python psycopg

module APIs2 The following are important psycopg2The official documentation of the module. The module, which can meet your needs for using PostgreSQL database in Python programs. If you are looking for more complex applications, you can check Python psycopg

S. No.	API & Description
1	psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432); This API opens a connection to the PostgreSQL database. If the database is successfully opened, it will return a connection object.
2	connection.cursor() Create a cursor, which will be used during the process of database programming with Python.
3	cursor.execute(sql [, optional parameters]) Execute SQL statements. SQL statements can be parameterized (i.e., placeholders instead of SQL text). psycopg2The module supports using %s placeholders with the %s symbol For example: cursor.execute("insert into people values (%s, %s)", (who, age))
4	cursor.executemany(sql, seq_of_parameters) Execute SQL commands for all parameter sequences or mappings found in the sequence SQL.
5	cursor.callproc(procname[, parameters]) Execute the stored database procedure with the given name. The sequence of parameters must contain an entry for each parameter expected by the procedure.
6	cursor.rowcount Read-only property, it returns the last time execute was executed*The total number of rows modified, inserted, or deleted by the database.
7	connection.commit() This method commits the current transaction. If this method is not called, any operation performed since the last call to commit() cannot be seen from other database connections.
8	connection.rollback() Return any changes made to the database since the last call to commit().
9	connection.close() This method closes the database connection. Note that it does not automatically call commit(). If you just close the database connection without calling commit() first, your changes will be lost!
10	cursor.fetchone() This method retrieves the next row from the query result set, returns a single sequence, or returns None when no more data is available.
11	cursor.fetchmany([size=cursor.arraysize]) Retrieve the next set of rows from the query result and return a list. An empty list is returned when no more rows are available. This method attempts to retrieve as many rows as indicated by the size parameter.
12	cursor.fetchall() Retrieve all (remaining) rows of the query result and return a list. An empty list is returned when no rows are available.

Connect to database

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

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database="testdb", user = "postgres", password = "pass123", host = \127.0.0.1", port = \5432);
print "Opened database successfully"

Here, you can also provide the database testdb as the name. If the database is opened successfully, it will display the following message

Open database successfully

Create table

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

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = \127.0.0.1", port = \5432);
print "Opened database successfully"
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);''')
print "Table created successfully"
conn.commit();
conn.close()

When the above program is executed, it will create the COMPANY table in test.db and display the following message

Opened database successfully
Table created successfully

INSERT operation

The following Python program demonstrates how to create records in the COMPANY table created in the above example

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = \127.0.0.1", port = \5432);
print "Opened database successfully"
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();
print "Records created successfully";
conn.close()

When the above program is executed, the specified records will be created in the COMPANY table, and the following two lines will be displayed

Opened database successfully
Records created successfully

SELECT operation

The following Python program shows how to retrieve and display records from the COMPANY table created in the above example

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = \127.0.0.1", port = \5432);
print "Opened database successfully"
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], 
"
print "Operation done successfully";
conn.close()

When the above program is executed, the following results will be produced

Opened database successfully
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
Operation done successfully

UPDATE operation

The following Python code demonstrates how to use the UPDATE statement to update any record and then retrieve and display records from the COMPANY table-Get and display the updated records

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = \127.0.0.1", port = \5432);
print "Opened database successfully"
cur = conn.cursor()
cur.execute("UPDATE COMPANY set SALARY =　25000.00 where ID =　1);
conn.commit();
print "Total number of rows updated:", cur.rowcount
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], 
"
print "Operation done successfully";
conn.close()

When the above program is executed, the following results will be produced

Opened database successfully
Total number of rows updated:　1
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
Operation done successfully

DELETE operation

The following Python code demonstrates how to use the DELETE statement to delete any record and then retrieve and display the remaining records from our COMPANY table

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = \127.0.0.1", port = \5432);
print "Opened database successfully"
cur = conn.cursor()
cur.execute("DELETE FROM COMPANY WHERE ID=")2);
conn.commit();
print "Total number of rows deleted :", cur.rowcount
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], 
"
print "Operation done successfully";
conn.close()

When the above program is executed, the following results will be produced

Opened database successfully
Total number of rows deleted :　1
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
Operation done successfully

PostgreSQL Perl