python-PostgreSQL
戚宏扬
下面展示一些 代码。
// An highlighted block
var foo = 'bar';
import psycopg2
#获得连接
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
#获得游标对象
cursor=conn.cursor()
#sql语句
sql='SELECT VERSION()'
#执行语句
cursor.execute(sql)
#获取单条数据
data=cursor.fetchone()
#打印
print('database version:%s'%data)
#事务提交
conn.commit()
#关闭数据库连接
conn.close()
#打印出数据库的版本说明数据库连接成功
#******创建表
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""CREATE TABLE STUDENT(
id serial PRIMARY KEY,
num INT4,
name VARCHAR(25));"""
cursor.execute(sql)
print("Create table student successfully!")
conn.commit()
conn.close()
#******插入操作
#插入数据形式1
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="INSERT INTO student(num,name) VALUES (100,'lucy');"
cursor.execute(sql)
print("insert data successfully!")
conn.commit()
conn.close()
#插入数据形式2——参数分离
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""INSERT INTO student(num,name) VALUES(%s,%s)"""
params=(101,'mike')
cursor.execute(sql,params)
print('insert data into student successfully!')
conn.commit()
conn.close()
#插入数据形式3——字典
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""INSERT INTO student(num,name) VALUES(%(num)s,%(name)s);"""
params={'num':121,'name':'paul'}
cursor.execute(sql,params)
print('insert data into student successfully!')
conn.commit()
conn.close()
#查询一条数据
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""SELECT * FROM student;"""
cursor.execute(sql)
#抓取
row=cursor.fetchone()
print(row)
conn.commit()
conn.close()
#查询多条数据
'''
1.使用fetchmany([size=cursor.arraysize])方法可以抓取多条数据；
2.此方法可以多次使用，直到数据库中没有数据，此时会返回空列表；
3.如果不传参数，会限制查询条数，一般就是返回第一条；
'''
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""SELECT * FROM student;"""
cursor.execute(sql)
rows=cursor.fetchmany(4)
print(rows)
conn.commit()
cursor.close()
conn.close()
#查询全部数据
#使用 fetchall() 方法会抓取所有数据
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""SELECT * FROM student;"""
cursor.execute(sql)
rows=cursor.fetchall()
print(rows)
conn.commit()
cursor.close()
conn.close()
#按条件查询数据
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""SELECT name FROM student;"""
sql="""SELECT * FROM student WHERE id>1;"""
sql="""SELECT * FROM student WHERE id=%s;"""
params=(1,)
cursor.execute(sql,params)
rows=cursor.fetchall()
print(rows)
conn.commit()
cursor.close()
conn.close()
#更新
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""UPDATE student SET name='小美女呀' WHERE id=3;"""
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
#删除
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""DELETE FROM student WHERE id=3;"""
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
#异常处理
#这个text数据表是不存在的
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""SELECT * FROM text"""
try:
cursor.execute(sql)
except psycopg2.Error as e:
print(e)
conn.commit()
cursor.close()
conn.close()
#打印sql,打印的是sql语句
#使用cursor.query 可以查看执行的sql语句，方便排查；
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""SELECT * FROM student"""
try:
cursor.execute(sql)
que=cursor.query
print(que)
except psycopg2.Error as e:
print(e)
conn.commit()
cursor.close()
conn.close()
#显示执行参数
#使用 mogrify(operation[, parameters]) 能够显示执行语句的参数绑定结果，返回的是字符串形式；
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""INSERT INTO student (num,name) VALUES(%s,%s)"""
params=(112,'达每秒')
result=cursor.mogrify(sql,params)
print(result.decode('utf-8'))
cursor.execute(sql,params)
conn.commit()
cursor.close()
conn.close()
#获取数据总条数
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""SELECT * FROM student"""
#sql里面的语句是"SELECT COUNT(*) FROM student"
cursor.execute(sql)
count=cursor.rowcount
print(count)
conn.commit()
cursor.close()
conn.close()
#显示行号
#使用cursor.rownumber 可以显示当前查询sql获得数据的行号，每抓取一次光标的索引就会加1；
import psycopg2
conn=psycopg2.connect(database='postgres',user='postgres',password='131415',host='127.0.0.1',port='5432')
cursor=conn.cursor()
sql="""SELECT * FROM student"""
cursor.execute(sql)
row1=cursor.fetchone()
print(cursor.rownumber)
row2=cursor.fetchone()
print(cursor.rownumber)
conn.commit()
cursor.close()
conn.close()
#sql和python语句的对应
'''
Python PostgreSQL
None NULL
bool bool
float real，double
int，long smallint，integer，bigint
Decimal numeric
str，unicode varchar，text
buffer，memoryview，bytearray，bytes，Buffer protocol bytea
date date
time time,timetz
datetime timestamp,timestamptz
timedelta interval
list ARRAY
tuple,namedtuple Composite typesIN syntax
dict hstore
Range range
UUID uuid
Anything json
ipaddress inet
'''
#转载自https://www.cnblogs.com/zszxz/p/12222201.html
类似资料：