Python Operation PostgreSQL

This article is about how to use Python to manipulate PostgreSQL, now here to share, a friend in need can refer to

Python Operation PostgreSQL

Prerequisites: The PostgreSQL database is installed on the user's computer

Install psycopg2–> pip Install PSYCOPG2
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 for example: Cursor.execute ("INSERT into people values (%s,%s)", (who, age))
4, Curosr.executemany (SQL, Seq_of_parameters) The program executes SQL commands on 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, inserting or deleting 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 from the next group's query results, 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

1. Connect to the database

Import PSYCOPG2  #导入相关模块 # incoming parameter database name User name user password host address Port conn = Psycopg2.connect (database= "MyBase", user= "Postgres", Password= "asd123456", host= "127.0.0.1", port= "5432") print "OK"

2. Create a table

Import Psycopg2conn = Psycopg2.connect (database= "MyBase", user= "Postgres", password= "asd123456", host= "127.0.0.1", Port= "5432") cur = conn.cursor () #建立操作游标 # incoming parameter is SQL build Table statement Cur.execute ("' CREATE Table company       (ID INT PRIMARY KEY     Not NULL, the       NAME TEXT is not    null, the age INT is not     null,       ADDRESS        CHAR,       SALARY         REAL); Print "OK" conn.commit ()  #注意, only a commit will take effect conn.close ()

3. Inserting data

Import Psycopg2conn = Psycopg2.connect (database= "MyBase", user= "Postgres", password= "asd123456", host= "127.0.0.1", Port= "5432") cur = conn.cursor () #让它ID自动生成cur. Execute ("INSERT into Company (name,age,address,salary) VALUES (' Paul ', 32, "California ', 20000.00)"); #注意这里还可以返回插入数据的IDresults = Cur.fetchone () ID = results[0]  #返回插入的记录的id easy to follow up Conn.commit () Conn.close ()

4. Delete data

Import Psycopg2conn = Psycopg2.connect (database= "MyBase", user= "Postgres", password= "asd123456", host= "127.0.0.1", Port= "5432") cur = conn.cursor () cur.execute ("DELETE from company where id=2;") Conn.commitconn.close ()

5. Update data

Import Psycopg2conn = Psycopg2.connect (database= "MyBase", user= "Postgres", password= "asd123456", host= "127.0.0.1", Port= "5432") cur = conn.cursor () cur.execute ("UPDATE company set SALARY = 25000.00 where id=1") Conn.commitconn.close ()

6. Query data

Import Psycopg2conn = Psycopg2.connect (database= "MyBase", user= "Postgres", password= "asd123456", host= "127.0.0.1", Port= "5432") cur = conn.cursor () cur.execute ("Select ID, name, address, salary from company  ") rows = Cur.fetchall () # This returns all data sets of the query Conn.close ()