PostgreSQL连接Python(1)(2)
INSERT 操作
Python程序显示了我们如何创建表COMPANY 在上面的例子中创建表中的记录:
- #!/usr/bin/python
- import psycopg2
- conn = 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', 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
SELECT 操作
Python程序,显示如何获取并显示COMPANY 表在上面的例子中创建的记录:
- #!/usr/bin/python
- import psycopg2
- conn = 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()
- 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
UPDATE 操作
Python代码显示如何,我们可以使用UPDATE语句来更新记录,然后从COMPANY表获取并显示更新的记录:
- #!/usr/bin/python
- import psycopg2
- conn = 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.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
评论关闭