Python uses the psycopg2 module to operate PostgreSQL, psycopg2postgresql

Python uses the psycopg2 module to operate PostgreSQL, psycopg2postgresql

In fact, there are many modules in Python that can be used to connect to PostgreSQL. Here we recommend psycopg2. Psycopg2 is very easy to install (pip install psycopg2). Here we will focus on how to use it.

Install the psycopg2 module:

How to verify whether psycopy2 has been installed?

Write the above Code and run it to check whether the psycopg2 module is missing.

Installation Method 1:

1)install with psycopg2-2.4.2.win-amd64-py2.7-pg9.0.4-release.exe,: http://vdisk.weibo.com/s/Cd8pPaw56Ozys

Run the exe directly without errors. If the verification code above is correct, the installation is complete.

2) How to uninstall it?

2.1) Find the installation directory: C: \ Python27 and find the following file: Removepsycopg2.exe. Run the file to delete it;

2.2) if the run fails, go to the directory: C: \ Python27 \ Lib \ site-packages, find the psycopg2 folder and psycopg2-2.4.2-py2.7.egg-info file, right-click to delete.

2.3) run the above Code to confirm whether the deletion is successful.

Installation Method 2:

Install with. whl,: https://pypi.python.org/pypi/psycopg2/

Download file: psycopg2-2.6.2-cp27-none-win_amd64.whl

Here I copy the psycopg2-2.6.2-cp27-none-win_amd64.whl to the Scripts folder under the installation directory.

Run the code in cmd: pip install C: \ Python27 \ Scripts \ psycopg2-2.6.2-cp27-none-win_amd64.whl

Run the above Code to confirm whether the deletion is successful.

Psycopg2:

Use the account postgres to create the test database testdb.

Refer to yiibai. comAPI:

S. N. API & Description

1 psycopg2.connect (database = "testdb", user = "s", 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.

2 connection. cursor ()

This program creates a cursor for the whole database to use Python programming.

3 cursor.exe cute (SQL [, optional parameters])

This routine executes SQL statements. Parameterized SQL statements (placeholders rather than SQL text ). The psycopg2 module supports the % s flag for placeholders.

Example: cursor.exe cute ("insert into people values (% s, % s)", (who, age ))

4 curosr.exe cuteters (SQL, seq_of_parameters)

This program executes SQL commands to map all parameter sequences or SQL sequences.

5 curosr. callproc (procname [, parameters])

The name given by the stored database program executed by this program. This program is expected to be a parameter. The Parameter order must contain an entry.

6 cursor. rowcount

This read-only attribute returns the total number of rows in the database modified, inserted or deleted last execute *().

7 connection. commit ()

This method commits the current transaction. If you do not call this method, No matter what changes are made, since the last call of commit () is invisible, it is connected from other databases.

8 connection. rollback ()

This method rolls back any changes to the database since the previous call to the commit () method.

9 connection. close ()

This method closes the database connection. Note that this does not automatically call commit (). If you just close the database connection without calling the commit () method, all changes will be lost!

10 cursor. fetchone ()

The next row of the query result set extracted by this method returns a sequence, or it is not available when no more data is available.

11 cursor. fetchmany ([size = cursor. arraysize])

This routine retrieves the number of rows in the query result of the next group and returns a list. If no record is found, an empty list is returned. This method tries to obtain the size parameter displayed by as many rows as possible.

12 cursor. fetchall ()

This routine gets all the query results (the remaining rows) and returns a list. If the row is empty, an empty list is returned.

Open the database connection:

import osimport sysimport psycopg2def connectPostgreSQL():conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")print 'connect successful!'if __name__=='__main__':connectPostgreSQL()

Create a table:

import osimport sysimport psycopg2def connectPostgreSQL():conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")print 'connect successful!'cursor=conn.cursor()cursor.execute('''create table public.member(id integer not null primary key,name varchar(32) not null,password varchar(32) not null,singal varchar(128))''')conn.commit()conn.close()print 'table public.member is created!'if __name__=='__main__':connectPostgreSQL()

Insert operation:

import os import sys import psycopg2  def connectPostgreSQL():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   print 'connect successful!'   cursor=conn.cursor()   cursor.execute('''create table public.member( id integer not null primary key, name varchar(32) not null, password varchar(32) not null, singal varchar(128) )''')   conn.commit()   conn.close()   print 'table public.member is created!' def insertOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("insert into public.member(id,name,password,singal)\ values(1,'member0','password0','signal0')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(2,'member1','password1','signal1')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(3,'member2','password2','signal2')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(4,'member3','password3','signal3')")   conn.commit()   conn.close()      print 'insert records into public.memmber successfully'    if __name__=='__main__':   #connectPostgreSQL()insertOperate()

Select Operation:

import os import sys import psycopg2  def connectPostgreSQL():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   print 'connect successful!'   cursor=conn.cursor()   cursor.execute('''create table public.member( id integer not null primary key, name varchar(32) not null, password varchar(32) not null, singal varchar(128) )''')   conn.commit()   conn.close()   print 'table public.member is created!'  def insertOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("insert into public.member(id,name,password,singal)\ values(1,'member0','password0','signal0')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(2,'member1','password1','signal1')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(3,'member2','password2','signal2')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(4,'member3','password3','signal3')")   conn.commit()   conn.close()      print 'insert records into public.memmber successfully'  def selectOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("select id,name,password,singal from public.member where id>2")   rows=cursor.fetchall()   for row in rows:     print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'   conn.close()    if __name__=='__main__':   #connectPostgreSQL()   #insertOperate()   selectOperate()

Result:

Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit (AMD64)] on win32Type "copyright", "credits" or "license()" for more information.>>> ========== RESTART: C:\Users\Administrator\Desktop\mutilpleTest.py ==========id= 3 ,name= member2 ,pwd= password2 ,singal= signal2 id= 4 ,name= member3 ,pwd= password3 ,singal= signal3 >>> 

Update operation:

 import os import sys import psycopg2  def connectPostgreSQL():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   print 'connect successful!'   cursor=conn.cursor()   cursor.execute('''create table public.member( id integer not null primary key, name varchar(32) not null, password varchar(32) not null, singal varchar(128) )''')   conn.commit()   conn.close()   print 'table public.member is created!'  def insertOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("insert into public.member(id,name,password,singal)\ values(1,'member0','password0','signal0')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(2,'member1','password1','signal1')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(3,'member2','password2','signal2')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(4,'member3','password3','signal3')")   conn.commit()   conn.close()      print 'insert records into public.memmber successfully'  def selectOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("select id,name,password,singal from public.member where id>2")   rows=cursor.fetchall()   for row in rows:     print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'   conn.close()  def updateOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("update public.member set name='update ...' where id=2")   conn.commit()   print "Total number of rows updated :", cursor.rowcount    cursor.execute("select id,name,password,singal from public.member")   rows=cursor.fetchall()   for row in rows:     print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'   conn.close()    if __name__=='__main__':   #connectPostgreSQL()   #insertOperate()   #selectOperate()   updateOperate()

Result:

Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit (AMD64)] on win32Type "copyright", "credits" or "license()" for more information.>>> ========== RESTART: C:\Users\Administrator\Desktop\mutilpleTest.py ==========Total number of rows updated : 1id= 1 ,name= member0 ,pwd= password0 ,singal= signal0 id= 3 ,name= member2 ,pwd= password2 ,singal= signal2 id= 4 ,name= member3 ,pwd= password3 ,singal= signal3 id= 2 ,name= update ... ,pwd= password1 ,singal= signal1 >>> 

Delete operation:

 import os import sys import psycopg2  def connectPostgreSQL():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   print 'connect successful!'   cursor=conn.cursor()   cursor.execute('''create table public.member( id integer not null primary key, name varchar(32) not null, password varchar(32) not null, singal varchar(128) )''')   conn.commit()   conn.close()   print 'table public.member is created!'  def insertOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("insert into public.member(id,name,password,singal)\ values(1,'member0','password0','signal0')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(2,'member1','password1','signal1')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(3,'member2','password2','signal2')")   cursor.execute("insert into public.member(id,name,password,singal)\ values(4,'member3','password3','signal3')")   conn.commit()   conn.close()      print 'insert records into public.memmber successfully'  def selectOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("select id,name,password,singal from public.member where id>2")   rows=cursor.fetchall()   for row in rows:     print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'   conn.close()  def updateOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")   cursor=conn.cursor()   cursor.execute("update public.member set name='update ...' where id=2")   conn.commit()   print "Total number of rows updated :", cursor.rowcount    cursor.execute("select id,name,password,singal from public.member")   rows=cursor.fetchall()   for row in rows:     print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'   conn.close()  def deleteOperate():   conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")     cursor=conn.cursor()    cursor.execute("select id,name,password,singal from public.member")   rows=cursor.fetchall()   for row in rows:     print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'    print 'begin delete'   cursor.execute("delete from public.member where id=2")   conn.commit()     print 'end delete'   print "Total number of rows deleted :", cursor.rowcount      cursor.execute("select id,name,password,singal from public.member")   rows=cursor.fetchall()   for row in rows:     print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'   conn.close()    if __name__=='__main__':   #connectPostgreSQL()   #insertOperate()   #selectOperate()   #updateOperate()   deleteOperate()

Result:

Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit (AMD64)] on win32Type "copyright", "credits" or "license()" for more information.>>> ========== RESTART: C:\Users\Administrator\Desktop\mutilpleTest.py ==========id= 1 ,name= member0 ,pwd= password0 ,singal= signal0 id= 3 ,name= member2 ,pwd= password2 ,singal= signal2 id= 4 ,name= member3 ,pwd= password3 ,singal= signal3 id= 2 ,name= update ... ,pwd= password1 ,singal= signal1 begin deleteend deleteTotal number of rows deleted : 1id= 1 ,name= member0 ,pwd= password0 ,singal= signal0 id= 3 ,name= member2 ,pwd= password2 ,singal= signal2 id= 4 ,name= member3 ,pwd= password3 ,singal= signal3 >>>