Python:使用psycopg2模块操作PostgreSQL
来源:互联网 发布:php提示错误信息代码 编辑:程序博客网 时间:2024/05/17 08:24
安装psycopg2模块:
- 怎么验证是否已经安装过psycopy2?
编写上面代码,运行看是否抛出缺少psycopg2模块。
- 安装方法1:
1)使用psycopg2-2.4.2.win-amd64-py2.7-pg9.0.4-release.exe安装,下载地址:http://vdisk.weibo.com/s/Cd8pPaw56Ozys
直接运行exe,不出错误,运行上边代码验证代码无错误,基本算是安装完成了。
2)怎么卸载?
2.1)找到安装目录:C:\Python27,发现下边包含文件:Removepsycopg2.exe,运行,来删除;
2.2)如果运行失败的话,进入目录:C:\Python27\Lib\site-packages下,找到psycopg2文件夹和psycopg2-2.4.2-py2.7.egg-info文件,右键删除。
2.3)运行上边的代码,确认是否删除成功。
- 安装方法2:
使用.whl安装,下载地址:https://pypi.python.org/pypi/psycopg2/
下载文件:psycopg2-2.6.2-cp27-none-win_amd64.whl
我这里把psycopg2-2.6.2-cp27-none-win_amd64.whl拷贝到安装目录下Scripts文件夹中。
cmd中运行代码:pip install C:\Python27\Scripts\psycopg2-2.6.2-cp27-none-win_amd64.whl
运行上边的代码,确认是否删除成功。
- 通过psycopg2操作数据库:
使用账户postgres,创建测试数据库testdb。
参考yiibai.comAPI:
S.N. API & 描述
1 psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432")
这个API打开一个连接到PostgreSQL数据库。如果成功打开数据库时,它返回一个连接对象。
2 connection.cursor()
该程序创建一个光标将用于整个数据库使用Python编程。
3 cursor.execute(sql [, optional parameters])
此例程执行SQL语句。可被参数化的SQL语句(即占位符,而不是SQL文字)。 psycopg2的模块支持占位符用%s标志
例如:cursor.execute("insert into people values (%s, %s)", (who, age))
4 curosr.executemany(sql, seq_of_parameters)
该程序执行SQL命令对所有参数序列或序列中的sql映射。
5 curosr.callproc(procname[, parameters])
这个程序执行的存储数据库程序给定的名称。该程序预计为每一个参数,参数的顺序必须包含一个条目。
6 cursor.rowcount
这个只读属性,它返回数据库中的行的总数已修改,插入或删除最后 execute*().
7 connection.commit()
此方法提交当前事务。如果不调用这个方法,无论做了什么修改,自从上次调用commit()是不可见的,从其他的数据库连接。
8 connection.rollback()
此方法会回滚任何更改数据库自上次调用commit()方法。
9 connection.close()
此方法关闭数据库连接。请注意,这并不自动调用commit()。如果你只是关闭数据库连接而不调用commit()方法首先,那么所有更改将会丢失!
10 cursor.fetchone()
这种方法提取的查询结果集的下一行,返回一个序列,或者无当没有更多的数据是可用的。
11 cursor.fetchmany([size=cursor.arraysize])
这个例程中取出下一个组的查询结果的行数,返回一个列表。当没有找到记录,返回空列表。该方法试图获取尽可能多的行所显示的大小参数。
12 cursor.fetchall()
这个例程获取所有查询结果(剩余)行,返回一个列表。空行时则返回空列表。
- 打开数据库连接:
1 import os 2 import sys 3 import psycopg2 4 5 def connectPostgreSQL(): 6 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432") 7 print 'connect successful!' 8 9 if __name__=='__main__':10 connectPostgreSQL()11
- 创建表操作:
1 import os 2 import sys 3 import psycopg2 4 5 def connectPostgreSQL(): 6 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432") 7 print 'connect successful!' 8 cursor=conn.cursor() 9 cursor.execute('''create table public.member(10 id integer not null primary key,11 name varchar(32) not null,12 password varchar(32) not null,13 singal varchar(128)14 )''')15 conn.commit()16 conn.close()17 print 'table public.member is created!'18 19 if __name__=='__main__':20 connectPostgreSQL()21
- Insert 操作:
1 import os 2 import sys 3 import psycopg2 4 5 def connectPostgreSQL(): 6 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432") 7 print 'connect successful!' 8 cursor=conn.cursor() 9 cursor.execute('''create table public.member(10 id integer not null primary key,11 name varchar(32) not null,12 password varchar(32) not null,13 singal varchar(128)14 )''')15 conn.commit()16 conn.close()17 print 'table public.member is created!'18 19 def insertOperate():20 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")21 cursor=conn.cursor()22 cursor.execute("insert into public.member(id,name,password,singal)\23 values(1,'member0','password0','signal0')")24 cursor.execute("insert into public.member(id,name,password,singal)\25 values(2,'member1','password1','signal1')")26 cursor.execute("insert into public.member(id,name,password,singal)\27 values(3,'member2','password2','signal2')")28 cursor.execute("insert into public.member(id,name,password,singal)\29 values(4,'member3','password3','signal3')")30 conn.commit()31 conn.close()32 33 print 'insert records into public.memmber successfully'34 35 if __name__=='__main__':36 #connectPostgreSQL()37 insertOperate()38
- Select 操作:
1 import os 2 import sys 3 import psycopg2 4 5 def connectPostgreSQL(): 6 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432") 7 print 'connect successful!' 8 cursor=conn.cursor() 9 cursor.execute('''create table public.member(10 id integer not null primary key,11 name varchar(32) not null,12 password varchar(32) not null,13 singal varchar(128)14 )''')15 conn.commit()16 conn.close()17 print 'table public.member is created!'18 19 def insertOperate():20 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")21 cursor=conn.cursor()22 cursor.execute("insert into public.member(id,name,password,singal)\23 values(1,'member0','password0','signal0')")24 cursor.execute("insert into public.member(id,name,password,singal)\25 values(2,'member1','password1','signal1')")26 cursor.execute("insert into public.member(id,name,password,singal)\27 values(3,'member2','password2','signal2')")28 cursor.execute("insert into public.member(id,name,password,singal)\29 values(4,'member3','password3','signal3')")30 conn.commit()31 conn.close()32 33 print 'insert records into public.memmber successfully'34 35 def selectOperate():36 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")37 cursor=conn.cursor()38 cursor.execute("select id,name,password,singal from public.member where id>2")39 rows=cursor.fetchall()40 for row in rows:41 print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'42 conn.close()43 44 if __name__=='__main__':45 #connectPostgreSQL()46 #insertOperate()47 selectOperate()48
结果:
Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit (AMD64)] on win32Type "copyright", "credits" or "license()" for more information.>>> ========== RESTART: C:\Users\Administrator\Desktop\mutilpleTest.py ==========id= 3 ,name= member2 ,pwd= password2 ,singal= signal2 id= 4 ,name= member3 ,pwd= password3 ,singal= signal3 >>>
- update操作:
1 import os 2 import sys 3 import psycopg2 4 5 def connectPostgreSQL(): 6 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432") 7 print 'connect successful!' 8 cursor=conn.cursor() 9 cursor.execute('''create table public.member(10 id integer not null primary key,11 name varchar(32) not null,12 password varchar(32) not null,13 singal varchar(128)14 )''')15 conn.commit()16 conn.close()17 print 'table public.member is created!'18 19 def insertOperate():20 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")21 cursor=conn.cursor()22 cursor.execute("insert into public.member(id,name,password,singal)\23 values(1,'member0','password0','signal0')")24 cursor.execute("insert into public.member(id,name,password,singal)\25 values(2,'member1','password1','signal1')")26 cursor.execute("insert into public.member(id,name,password,singal)\27 values(3,'member2','password2','signal2')")28 cursor.execute("insert into public.member(id,name,password,singal)\29 values(4,'member3','password3','signal3')")30 conn.commit()31 conn.close()32 33 print 'insert records into public.memmber successfully'34 35 def selectOperate():36 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")37 cursor=conn.cursor()38 cursor.execute("select id,name,password,singal from public.member where id>2")39 rows=cursor.fetchall()40 for row in rows:41 print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'42 conn.close()43 44 def updateOperate():45 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")46 cursor=conn.cursor()47 cursor.execute("update public.member set name='update ...' where id=2")48 conn.commit()49 print "Total number of rows updated :", cursor.rowcount50 51 cursor.execute("select id,name,password,singal from public.member")52 rows=cursor.fetchall()53 for row in rows:54 print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'55 conn.close()56 57 if __name__=='__main__':58 #connectPostgreSQL()59 #insertOperate()60 #selectOperate()61 updateOperate()
结果:
Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit (AMD64)] on win32Type "copyright", "credits" or "license()" for more information.>>> ========== RESTART: C:\Users\Administrator\Desktop\mutilpleTest.py ==========Total number of rows updated : 1id= 1 ,name= member0 ,pwd= password0 ,singal= signal0 id= 3 ,name= member2 ,pwd= password2 ,singal= signal2 id= 4 ,name= member3 ,pwd= password3 ,singal= signal3 id= 2 ,name= update ... ,pwd= password1 ,singal= signal1 >>>
- Delete操作:
1 import os 2 import sys 3 import psycopg2 4 5 def connectPostgreSQL(): 6 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432") 7 print 'connect successful!' 8 cursor=conn.cursor() 9 cursor.execute('''create table public.member(10 id integer not null primary key,11 name varchar(32) not null,12 password varchar(32) not null,13 singal varchar(128)14 )''')15 conn.commit()16 conn.close()17 print 'table public.member is created!'18 19 def insertOperate():20 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")21 cursor=conn.cursor()22 cursor.execute("insert into public.member(id,name,password,singal)\23 values(1,'member0','password0','signal0')")24 cursor.execute("insert into public.member(id,name,password,singal)\25 values(2,'member1','password1','signal1')")26 cursor.execute("insert into public.member(id,name,password,singal)\27 values(3,'member2','password2','signal2')")28 cursor.execute("insert into public.member(id,name,password,singal)\29 values(4,'member3','password3','signal3')")30 conn.commit()31 conn.close()32 33 print 'insert records into public.memmber successfully'34 35 def selectOperate():36 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")37 cursor=conn.cursor()38 cursor.execute("select id,name,password,singal from public.member where id>2")39 rows=cursor.fetchall()40 for row in rows:41 print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'42 conn.close()43 44 def updateOperate():45 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432")46 cursor=conn.cursor()47 cursor.execute("update public.member set name='update ...' where id=2")48 conn.commit()49 print "Total number of rows updated :", cursor.rowcount50 51 cursor.execute("select id,name,password,singal from public.member")52 rows=cursor.fetchall()53 for row in rows:54 print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'55 conn.close()56 57 def deleteOperate():58 conn = psycopg2.connect(database="testdb", user="postgres", password="new.1234", host="127.0.0.1", port="5432") 59 cursor=conn.cursor()60 61 cursor.execute("select id,name,password,singal from public.member")62 rows=cursor.fetchall()63 for row in rows:64 print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'65 66 print 'begin delete'67 cursor.execute("delete from public.member where id=2")68 conn.commit() 69 print 'end delete'70 print "Total number of rows deleted :", cursor.rowcount71 72 cursor.execute("select id,name,password,singal from public.member")73 rows=cursor.fetchall()74 for row in rows:75 print 'id=',row[0], ',name=',row[1],',pwd=',row[2],',singal=',row[3],'\n'76 conn.close()77 78 if __name__=='__main__':79 #connectPostgreSQL()80 #insertOperate()81 #selectOperate()82 #updateOperate()83 deleteOperate()
结果:
Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit (AMD64)] on win32Type "copyright", "credits" or "license()" for more information.>>> ========== RESTART: C:\Users\Administrator\Desktop\mutilpleTest.py ==========id= 1 ,name= member0 ,pwd= password0 ,singal= signal0 id= 3 ,name= member2 ,pwd= password2 ,singal= signal2 id= 4 ,name= member3 ,pwd= password3 ,singal= signal3 id= 2 ,name= update ... ,pwd= password1 ,singal= signal1 begin deleteend deleteTotal number of rows deleted : 1id= 1 ,name= member0 ,pwd= password0 ,singal= signal0 id= 3 ,name= member2 ,pwd= password2 ,singal= signal2 id= 4 ,name= member3 ,pwd= password3 ,singal= signal3 >>>
- Python:使用psycopg2模块操作PostgreSQL
- Python通过psycopg2操作PostgreSQL
- 使用psycopg2操作PostgreSQL数据库之二
- Python调用postgreSQL(使用psycopg2)
- centos5.8安装postgresql以及python模块psycopg2
- Python+PostgreSQL+Postgis+Psycopg2+GeoDjango Installer
- postgresql Psycopg2
- ubuntu 使用、配置postgresql以及psycopg2
- PYthon psycopg2 数据库使用学习
- python访问PostgreSQL数据库之连接库Psycopg2
- 【Python相关文档】Postgresql数据库接口 - psycopg2,aiopy
- Psycopg2使用
- python安装psycopg2模块失败时的一种解决方法
- Python:操作PostgreSQL数据库(使用PyGreSQL)
- Python——使用psycopy2操作PostgreSQL
- mac python 安装 psycopg2
- 在 Mac 下Django 使用PostgreSQL 出现的 No module named psycopg2
- Python操作PostgreSQL
- 最近一直用Myeclipse2014写东西,今天打开突然闪退,电脑重启无效果
- 判断单链表中是否有环,找到环的入口节点
- STM32看门狗
- java设计模式之原型模式
- 最有用的BOM对象之一——location对象
- Python:使用psycopg2模块操作PostgreSQL
- 二进制文件的I/O
- 洛谷11月月赛 T2 不开心的金明
- java数组实题
- STL之拷贝和替换算法
- 从倒数第一到全国第二 安徽省专利电子申请率7年增8.4倍
- 原生 JS 懒加载简单实现
- 怎么修改eclipse及jsp的编码
- 一直程序员的养成日记 第一章 第五节 识好文,转好文