Python Operation PostgreSQL

Pip Install PSYCOPG

Python PSYCOPG2 Module APIs

The following is an important module routine for PSYCOPG2 to meet the Python program's work with the PostgreSQL database.

S.N.	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 returns a Connection object. www.yiibai.com
2	Connection.cursor () The program creates a cursor that will be used for the entire database using Python programming. yiibai.com
3	Cursor.execute (SQL [, optional parameters]) This routine executes the SQL statement. SQL statements that can be parameterized (that is, placeholders, not SQL text). The PSYCOPG2 module supports placeholders with the%s flag yiibai.com Example: Cursor.execute ("INSERT into people values (%s,%s)", (who, age))
4	Curosr.executemany (SQL, seq_of_parameters) The program executes a SQL command against all parameter sequences or SQL mappings in a sequence. www.yiibai.com
5	Curosr.callproc (procname[, parameters]) This program executes the stored database program given the name. The program is expected for each parameter, and the order of the parameters must contain an entry.
6	Cursor.rowcount This read-only property, which returns the total number of rows in the database has been modified, inserts or deletes the last execute* ().
7	Connection.commit () This method submits the current transaction. If you do not call this method, no matter what changes have been made, since the last Call commit () is not visible from the other database connection.
8	Connection.rollback () This method rolls back any changes to the database since the last call to the commit () method.
9	Connection.close () This method closes the database connection. Note that this does not automatically invoke commit (). If you just close the database connection without calling the commit () method first, then all changes will be lost! www.yiibai.com
10	Cursor.fetchone () This method extracts the next row of the query result set, returns a sequence, or none when no more data is available.
11	Cursor.fetchmany ([size=cursor.arraysize]) This routine takes the number of rows of the query result for the next group and returns a list. When no records are found, an empty list is returned. The method attempts to get as many rows as the size parameter is displayed.
12	Cursor.fetchall () This routine gets all the query results (remaining) rows and returns a list. An empty list is returned when the row is empty. www.yiibai.com

Connecting to a database

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

#!/usr/bin/pythonImportPsycopg2conn= Psycopg2.connect (database="TestDB", user="Postgres", password="pass123", host="127.0.0.1", port="5432")Print "opened database successfully" 

Create a table

The following Python program creates a table using the previously created database:

#!/usr/bin/pythonImportPsycopg2conn= 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 isn't null, NAME TEXT NOT NULL, age INT not NULL, ADDRESS CHAR (+), SALARY REAL);" ")Print "Table created successfully"Conn.commit () conn.close ( )

INSERT operation

The Python program shows how we created the table company in the above example to create the records in the table:

#!/usr/bin/pythonImportPsycopg2conn= 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 ', +, ' California ', 20000.00)"); Cur.execute ("INSERT into Company (id,name,age,address,salary) VALUES (2, ' Allen ', +, ' Texas ', 15000.00)"); Cur.execute ("INSERT into company VALUES (3, ' Teddy ', id,name,age,address,salary, ' Norway ', 20000.00)"); Cur.execute ("INSERT into Company (id,name,age,address,salary) VALUES (4, ' Mark ', ' Rich-mond ', 65000.00)"); Conn.commit ()Print "Records created successfully"; Conn.close ()

SELECT operation

A Python program that shows how to get and display the records created by the company table in the example above:

#!/usr/bin/pythonImportPsycopg2conn= 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 () forRowinchrows:Print "ID =", Row[0]Print "NAME =", row[1]   Print "ADDRESS =", row[2]   Print "SALARY =", Row[3],"\ n"Print "Operation Done successfully"; Conn.close ()

When the above program executes, it produces the following results

=  1  = = = 20000.0= 2 = =            =  15000.0  =3  = =20000.0     =  4=  =  rich-=  65000.0operation done Successfully

UPDATE operation

The Python code shows how we can use the UPDATE statement to update the record and then get and display the updated record from the company table

#!/usr/bin/pythonImportPsycopg2conn= 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.commitPrint "total number of rows updated:", Cur.rowcountcur.execute ("SELECT ID, name, address, salary from company") Rows=Cur.fetchall () forRowinchrows:Print "ID =", Row[0]Print "NAME =", row[1]   Print "ADDRESS =", row[2]   Print "SALARY =", Row[3],"\ n"Print "Operation Done successfully"; Conn.close ()

DELETE operation

The Python code shows how we can use the DELETE statement to delete a record and then get and display the remaining records for the company table:

#!/usr/bin/pythonImportPsycopg2conn= 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.commitPrint "total number of rows deleted:", Cur.rowcountcur.execute ("SELECT ID, name, address, salary from company") Rows=Cur.fetchall () forRowinchrows:Print "ID =", Row[0]Print "NAME =", row[1]   Print "ADDRESS =", row[2]   Print "SALARY =", Row[3],"\ n"Print "Operation Done successfully"; Conn.close ()

Python Operation PostgreSQL