最全总结 | 聊聊 Python 数据处理全家桶（PgSQL 篇）

# 安装依赖包pip3 install psycopg2

import psycopg2
# 获得连接对象# database：数据库名称# user：用户名# password：密码# host：数据库ip地址# port：端口号，默认为5432conn = psycopg2.connect(database="db_name", user="postgres", password="pwd", host="127.0.0.1", port="5432")
# 获取游标对象cursor = conn.cursor()

# 构建SQL语句# 方式一：直带参数​ sql = "INSERT INTO student (name,age) \                        VALUES (%s, '%s')" % \          ('xag',23)
# 方式二：参数分离sql = """INSERT INTO student (name,age) VALUES (%s, %s)"""# 参数params = ('xag',23)

# 执行sql# 注意：params可选，根据上面的参数方式来选择设置​cursor.execute(sql,[params])

 # 事务提交conn.commit()

# 释放游标对象及数据库连接对象cursor.close()conn.close()

# 获取一条记录one_data = cursor.fetchone()print(one_data)
# 获取2条记录many_data = cursor.fetchmany(2)print(many_data)
# 获取全部数据all_data = cursor.fetchall()print(all_data)

# 条件查询 SQL语句sql = """SELECT * FROM student where id = %s;"""
# 对应参数，参数结尾以逗号结尾​params = (1,)
# 执行SQLcursor.execute(sql, params)
# 获取所有数据datas = cursor.fetchall()print(datas)

def update_one(conn, cursor):    """更新操作"""    # 更新语句    sql = """update student set name = %s where id = %s  """    params = ('AirPython', 1,)
    # 执行语句    cursor.execute(sql, params)
    # 事务提交    conn.commit()
    # 关闭数据库连接    cursor.close()    conn.close()

​def delete_one(conn, cursor):    """删除操作"""    # 语句及参数    sql = """delete from  student where id = %s  """    params = (1,)
    # 执行语句    cursor.execute(sql, params)
    # 事物提交    conn.commit()
    # 关闭数据库连接    cursor.close()    conn.close()