Python operates PostgreSQL

This article introduces how to use Python to operate PostgreSQL. Now I will share it here. Friends in need can refer to it

Python to operate PostgreSQL

Prerequisite: User computer PostgreSQL database installed on

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 opened successfully, it returns a connection object. www.yiibai.com
2. connection.cursor() This 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 SQL statements. SQL statements that can be parameterized (i.e. placeholders, not SQL literals). The module of psycopg2 supports placeholders marked with %s. yiibai.com For example: cursor.execute(“insert into people values ​​(%s, %s)”, (who, age))
4. curosr.executemany(sql, seq_of_parameters) This program executes SQL commands to map all parameter sequences or sql in the sequence. www.yiibai.com
5. curosr.callproc(procname[, parameters]) This program executes the storage database program with the given name. The program expects an entry for each argument, and the order of arguments must contain one.
6. Cursor.rowcount is a read-only property that returns the total number of rows in the database that have been modified, inserted or deleted by the 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 to commit(), they will not be visible from other database connections.
8. Connection.rollback() This method will roll back any changes to the database since the last time the commit() method was called.
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 first, then all changes will be lost! www.yiibai.com
10. cursor.fetchone() This method extracts the next row of the query result set, returning a sequence, or nothing when no more data is available.
11. Cursor.fetchmany([size=cursor.arraysize]) In this routine, the number of rows of the query result of the next group is taken out and a list is returned. When no record is found, an empty list is returned. This method attempts to obtain as many rows as possible within the displayed size parameter.
12. cursor.fetchall() This routine obtains all query result (remaining) rows and returns a list. If there is an empty row, an empty list is returned. www.yiibai.com

1. Connect to the database

import psycopg2  #导入相关模块
#传入参数 数据库名 用户名 用户密码 主机地址 端口
conn = psycopg2.connect(database="mybase", user="postgres", password="asd123456", host="127.0.0.1", port="5432")

print "Ok"

2. Create a table

import psycopg2

conn = psycopg2.connect(database="mybase", user="postgres", password="asd123456", host="127.0.0.1", port="5432")

cur = conn.cursor() #建立操作游标
#传入的参数是 SQL 建表语句
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 "ok"

conn.commit()  #注意，只有commit一下才会生效
conn.close()

3. Insert data

import psycopg2

conn = 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 )");
#注意这里还可以返回插入数据的ID
results = cur.fetchone()
ID = results[0]  #返回插入的记录的id 便于后续操作

conn.commit()
conn.close()

4. Delete data

import psycopg2

conn = 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.commit
conn.close()

5. Update data

import psycopg2

conn = 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.commit
conn.close()

6. Query data

import psycopg2

conn = 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() #这里返回查询的所有数据集
conn.close()

Related recommendations:

How to connect PostgreSQL database with Python

php uses PDO extension to connect to PostgreSQL object relational database

php to connect to postgreSql

The above is the detailed content of Python operates PostgreSQL. For more information, please follow other related articles on the PHP Chinese website!