当前位置：Gxlcms > 数据库问题 > PostgreSQL连接python，postgresql在python 连接，创建表，创建表内容，插入操作，选择操作，更新操作，删除操作。

PostgreSQL连接python，postgresql在python 连接，创建表，创建表内容，插入操作，选择操作，更新操作，删除操作。

时间：2021-07-01 10:21:17 帮助过：2人阅读

!/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(‘‘‘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()

上述程序执行时，它会创建表COMPANY 在数据库test.db中，它会显示以下消息：

 

  1. <span style="color: #000000; font-size: 15px;">Opened database successfully
  2. Table created successfully</span>

INSERT 操作

Python程序显示了我们如何创建表COMPANY 在上面的例子中创建表中的记录：

  1. <span style="font-size: 15px;"><span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/python</span>
  2. <span style="color: #0000ff;">import</span><span style="color: #000000;"> psycopg2
  3. conn </span>= psycopg2.connect(database=<span style="color: #800000;">"</span><span style="color: #800000;">testdb</span><span style="color: #800000;">"</span>, user=<span style="color: #800000;">"</span><span style="color: #800000;">postgres</span><span style="color: #800000;">"</span>, password=<span style="color: #800000;">"</span><span style="color: #800000;">pass123</span><span style="color: #800000;">"</span>, host=<span style="color: #800000;">"</span><span style="color: #800000;">127.0.0.1</span><span style="color: #800000;">"</span>, port=<span style="color: #800000;">"</span><span style="color: #800000;">5432</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  4. </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Opened database successfully</span><span style="color: #800000;">"</span><span style="color: #000000;">
  5. cur </span>=<span style="color: #000000;"> conn.cursor()
  6. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)       VALUES (1, ‘Paul‘, 32, ‘California‘, 20000.00 )</span><span style="color: #800000;">"</span><span style="color: #000000;">);
  7. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)       VALUES (2, ‘Allen‘, 25, ‘Texas‘, 15000.00 )</span><span style="color: #800000;">"</span><span style="color: #000000;">);
  8. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)       VALUES (3, ‘Teddy‘, 23, ‘Norway‘, 20000.00 )</span><span style="color: #800000;">"</span><span style="color: #000000;">);
  9. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)       VALUES (4, ‘Mark‘, 25, ‘Rich-Mond ‘, 65000.00 )</span><span style="color: #800000;">"</span><span style="color: #000000;">);
  10. conn.commit()
  11. </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Records created successfully</span><span style="color: #800000;">"</span></span><span style="color: #000000;"><span style="font-size: 15px;">;
  12. conn.close() yiibai.com</span> </span>

上述程序执行时，它会创建COMPANY表中的记录，并显示以下两行：

  1. <span style="color: #000000; font-size: 15px;">Opened database successfully
  2. Records created successfully</span>

SELECT 操作

Python程序，显示如何获取并显示COMPANY 表在上面的例子中创建的记录：

  1. <span style="font-size: 15px;"><span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/python</span>
  2. <span style="color: #0000ff;">import</span><span style="color: #000000;"> psycopg2
  3. conn </span>= psycopg2.connect(database=<span style="color: #800000;">"</span><span style="color: #800000;">testdb</span><span style="color: #800000;">"</span>, user=<span style="color: #800000;">"</span><span style="color: #800000;">postgres</span><span style="color: #800000;">"</span>, password=<span style="color: #800000;">"</span><span style="color: #800000;">pass123</span><span style="color: #800000;">"</span>, host=<span style="color: #800000;">"</span><span style="color: #800000;">127.0.0.1</span><span style="color: #800000;">"</span>, port=<span style="color: #800000;">"</span><span style="color: #800000;">5432</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  4. </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Opened database successfully</span><span style="color: #800000;">"</span><span style="color: #000000;">
  5. cur </span>=<span style="color: #000000;"> conn.cursor()
  6. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">SELECT id, name, address, salary  from COMPANY</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  7. rows </span>=<span style="color: #000000;"> cur.fetchall()
  8. </span><span style="color: #0000ff;">for</span> row <span style="color: #0000ff;">in</span><span style="color: #000000;"> rows:
  9.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">ID = </span><span style="color: #800000;">"</span><span style="color: #000000;">, row[0]
  10.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">NAME = </span><span style="color: #800000;">"</span>, row[1<span style="color: #000000;">]
  11.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">ADDRESS = </span><span style="color: #800000;">"</span>, row[2<span style="color: #000000;">]
  12.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">SALARY = </span><span style="color: #800000;">"</span>, row[3], <span style="color: #800000;">"</span><span style="color: #800000;">\n</span><span style="color: #800000;">"</span>
  13. <span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Operation done successfully</span><span style="color: #800000;">"</span></span><span style="color: #000000;"><span style="font-size: 15px;">;
  14. conn.close() </span> </span>

当上述程序执行时，它会产生以下结果：

  1. <span style="font-size: 15px;"><span style="color: #000000;">Opened database successfully
  2. ID </span>=  1<span style="color: #000000;">
  3. NAME </span>=<span style="color: #000000;">  Paul
  4. ADDRESS </span>=<span style="color: #000000;">  California
  5. SALARY </span>=  20000.0<span style="color: #000000;">
  6. ID </span>=  2<span style="color: #000000;">
  7. NAME </span>=<span style="color: #000000;">  Allen
  8. ADDRESS </span>=<span style="color: #000000;">  Texas
  9. SALARY </span>=  15000.0<span style="color: #000000;">
  10. ID </span>=  3<span style="color: #000000;">
  11. NAME </span>=<span style="color: #000000;">  Teddy
  12. ADDRESS </span>=<span style="color: #000000;">  Norway
  13. SALARY </span>=  20000.0<span style="color: #000000;">
  14. ID </span>=  4<span style="color: #000000;">
  15. NAME </span>=<span style="color: #000000;">  Mark
  16. ADDRESS </span>=  Rich-<span style="color: #000000;">Mond
  17. SALARY </span>=  65000.0<span style="color: #000000;">
  18. Operation done successfully</span></span>

UPDATE 操作

Python代码显示如何，我们可以使用UPDATE语句来更新记录，然后从COMPANY表获取并显示更新的记录：

  1. <span style="font-size: 15px;"><span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/python</span>
  2. <span style="color: #0000ff;">import</span><span style="color: #000000;"> psycopg2
  3. conn </span>= psycopg2.connect(database=<span style="color: #800000;">"</span><span style="color: #800000;">testdb</span><span style="color: #800000;">"</span>, user=<span style="color: #800000;">"</span><span style="color: #800000;">postgres</span><span style="color: #800000;">"</span>, password=<span style="color: #800000;">"</span><span style="color: #800000;">pass123</span><span style="color: #800000;">"</span>, host=<span style="color: #800000;">"</span><span style="color: #800000;">127.0.0.1</span><span style="color: #800000;">"</span>, port=<span style="color: #800000;">"</span><span style="color: #800000;">5432</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  4. </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Opened database successfully</span><span style="color: #800000;">"</span><span style="color: #000000;">
  5. cur </span>=<span style="color: #000000;"> conn.cursor()
  6. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">UPDATE COMPANY set SALARY = 25000.00 where ID=1</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  7. conn.commit
  8. </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Total number of rows updated :</span><span style="color: #800000;">"</span><span style="color: #000000;">, cur.rowcount
  9. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">SELECT id, name, address, salary  from COMPANY</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  10. rows </span>=<span style="color: #000000;"> cur.fetchall()
  11. </span><span style="color: #0000ff;">for</span> row <span style="color: #0000ff;">in</span><span style="color: #000000;"> rows:
  12.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">ID = </span><span style="color: #800000;">"</span><span style="color: #000000;">, row[0]
  13.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">NAME = </span><span style="color: #800000;">"</span>, row[1<span style="color: #000000;">]
  14.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">ADDRESS = </span><span style="color: #800000;">"</span>, row[2<span style="color: #000000;">]
  15.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">SALARY = </span><span style="color: #800000;">"</span>, row[3], <span style="color: #800000;">"</span><span style="color: #800000;">\n</span><span style="color: #800000;">"</span>
  16. <span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Operation done successfully</span><span style="color: #800000;">"</span></span><span style="color: #000000;"><span style="font-size: 15px;">;
  17. conn.close()</span> </span>

当上述程序执行时，它会产生以下结果：

  1. <span style="font-size: 15px;"><span style="color: #000000;">Opened database successfully
  2. Total number of rows updated : </span>1<span style="color: #000000;">
  3. ID </span>=  1<span style="color: #000000;">
  4. NAME </span>=<span style="color: #000000;">  Paul
  5. ADDRESS </span>=<span style="color: #000000;">  California
  6. SALARY </span>=  25000.0<span style="color: #000000;">
  7. ID </span>=  2<span style="color: #000000;">
  8. NAME </span>=<span style="color: #000000;">  Allen
  9. ADDRESS </span>=<span style="color: #000000;">  Texas
  10. SALARY </span>=  15000.0<span style="color: #000000;">
  11. ID </span>=  3<span style="color: #000000;">
  12. NAME </span>=<span style="color: #000000;">  Teddy
  13. ADDRESS </span>=<span style="color: #000000;">  Norway
  14. SALARY </span>=  20000.0<span style="color: #000000;">
  15. ID </span>=  4<span style="color: #000000;">
  16. NAME </span>=<span style="color: #000000;">  Mark
  17. ADDRESS </span>=  Rich-<span style="color: #000000;">Mond
  18. SALARY </span>=  65000.0<span style="color: #000000;">
  19. Operation done successfully</span></span>

DELETE 操作

Python代码显示了如何我们可以使用DELETE语句删除记录，然后获取并显示COMPANY 表剩余的记录： 

  1. <span style="font-size: 15px;"><span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/python</span>
  2. <span style="color: #0000ff;">import</span><span style="color: #000000;"> psycopg2
  3. conn </span>= psycopg2.connect(database=<span style="color: #800000;">"</span><span style="color: #800000;">testdb</span><span style="color: #800000;">"</span>, user=<span style="color: #800000;">"</span><span style="color: #800000;">postgres</span><span style="color: #800000;">"</span>, password=<span style="color: #800000;">"</span><span style="color: #800000;">pass123</span><span style="color: #800000;">"</span>, host=<span style="color: #800000;">"</span><span style="color: #800000;">127.0.0.1</span><span style="color: #800000;">"</span>, port=<span style="color: #800000;">"</span><span style="color: #800000;">5432</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  4. </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Opened database successfully</span><span style="color: #800000;">"</span><span style="color: #000000;">
  5. cur </span>=<span style="color: #000000;"> conn.cursor()
  6. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">DELETE from COMPANY where ID=2;</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  7. conn.commit
  8. </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Total number of rows deleted :</span><span style="color: #800000;">"</span><span style="color: #000000;">, cur.rowcount
  9. cur.execute(</span><span style="color: #800000;">"</span><span style="color: #800000;">SELECT id, name, address, salary  from COMPANY</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  10. rows </span>=<span style="color: #000000;"> cur.fetchall()
  11. </span><span style="color: #0000ff;">for</span> row <span style="color: #0000ff;">in</span><span style="color: #000000;"> rows:
  12.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">ID = </span><span style="color: #800000;">"</span><span style="color: #000000;">, row[0]
  13.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">NAME = </span><span style="color: #800000;">"</span>, row[1<span style="color: #000000;">]
  14.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">ADDRESS = </span><span style="color: #800000;">"</span>, row[2<span style="color: #000000;">]
  15.    </span><span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">SALARY = </span><span style="color: #800000;">"</span>, row[3], <span style="color: #800000;">"</span><span style="color: #800000;">\n</span><span style="color: #800000;">"</span>
  16. <span style="color: #0000ff;">print</span> <span style="color: #800000;">"</span><span style="color: #800000;">Operation done successfully</span><span style="color: #800000;">"</span></span><span style="color: #000000;"><span style="font-size: 15px;">;
  17. conn.close() </span> </span>
  1. <span style="color: #000000;">当上述程序执行时，它会产生以下结果：
  2. <span style="font-size: 15px;">Opened database successfully
  3. Total number of rows deleted : </span></span><span style="font-size: 15px;">1<span style="color: #000000;">
  4. ID </span>=  1<span style="color: #000000;">
  5. NAME </span>=<span style="color: #000000;">  Paul
  6. ADDRESS </span>=<span style="color: #000000;">  California
  7. SALARY </span>=  20000.0<span style="color: #000000;">
  8. ID </span>=  3<span style="color: #000000;">
  9. NAME </span>=<span style="color: #000000;">  Teddy
  10. ADDRESS </span>=<span style="color: #000000;">  Norway
  11. SALARY </span>=  20000.0<span style="color: #000000;">
  12. ID </span>=  4<span style="color: #000000;">
  13. NAME </span>=<span style="color: #000000;">  Mark
  14. ADDRESS </span>=  Rich-<span style="color: #000000;">Mond
  15. SALARY </span>=  65000.0</span><span style="color: #000000;"><span style="font-size: 15px;">
  16. Operation done successfully</span> </span>

 

 

 

PostgreSQL连接python，postgresql在python 连接，创建表，创建表内容，插入操作，选择操作，更新操作，删除操作。

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！