postgresql python 接口——迹忆客-ag捕鱼王app官网

安装

postgresql 可以使用 psycopg2 模块与 python 集成。sycopg2 是 python 编程语言的 postgresql 数据库适配器。编写 psycopg2 的目的是使其轻便和快速，并且稳定。我们不需要单独安装此模块，因为默认情况下它随 python 2.5.x 版一起提供。

如果你的机器上没有安装它，那么你可以使用 yum 命令来安装它，如下所示 -

$yum install python-psycopg2

要使用 psycopg2 模块，必须首先创建一个代表数据库的 connection 对象，然后选择创建游标对象，这将对你执行所有 sql 语句有很大的帮助。

连接到数据库

以下 python 代码显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。

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

在这里，您还可以提供数据库 jiyik_db 作为名称，如果数据库成功连接，则会显示以下消息

open database successfully

创建表

以下 python 程序将用于在先前创建的数据库中创建表

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "jiyik_db", 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()

当上面给定的程序被执行时，它会在你的 jiyik_db 中创建 company 表，并且显示以下消息

opened database successfully
table created successfully

插入操作

以下 python 程序显示了如何在上面示例中创建的 company 表中创建记录

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "jiyik_db", 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()

当执行上述给定的程序时，它将在 company 表中创建给定的记录，并显示以下两行

opened database successfully
records created successfully

选择操作

以下 python 程序显示了我们如何从上面示例中创建的 company 表中获取数据，并且显示记录

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "jiyik_db", 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], "\n"
print "operation done successfully";
conn.close()

当上面给定的程序被执行时，它会产生以下结果

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

更新操作

下面的 python 代码展示了我们如何使用 update 语句来更新任何记录，然后从我们的 company 表中获取更新后的数据，并显示出来

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "jiyik_db", 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], "\n"
print "operation done successfully";
conn.close()

当上面给定的程序被执行时，它会产生以下结果

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

删除操作

下面的 python 代码展示了我们如何使用 delete 语句删除记录，然后从我们的 company 表中获取并显示剩余的记录

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "jiyik_db", 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], "\n"
print "operation done successfully";
conn.close()

当上面给定的程序被执行时，它会产生以下结果 -

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