用 python + PostgreSQL 实现个人数据库

第一步：下载、安装SQL数据库软件，win系统需要下载32位的，64位的安装过程会报错

第二步：安装python，这里选择使用anaconda作为python的管理软件

第三步：安装pycharm，并设置pycharm的环境为anaconda-python.exe。

使用以下脚本代码进行测试验证

# -*- coding: utf-8 -*-
import psycopg2
# 获得连接
conn = psycopg2.connect(database="postgres", user="postgres", password="5432", 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)
# 创建一个数据表
# cursor.execute('''
#     create table public .scoreList(
#     id integer not null primary key,
#     name varchar(32) not null,
#     grade integer not null
#     )''')
# 增加一条数据
# cursor.execute("insert into public .scoreList(id,name,grade)\
#     values (10,'Shawn.zhang','100') ")

# 打印所有数据
cursor.execute("select id,name,grade from public.scoreList")
allData = cursor.fetchall()
for data in allData:
    print(data)

# 更改id=5的数据
cursor.execute("update public.scoreList set name = 'Sean.li' where id = 5")
print("after update :")

# 再次打印所有数据
cursor.execute("select id,name,grade from public.scoreList")
allData = cursor.fetchall()
for data in allData:
    print(data)

# 删掉id = 1的数据
cursor.execute("delete from public.scoreList where id = 1")
print("after delete :")

# 再次打印所有数据
cursor.execute("select id,name,grade from public.scoreList")
allData = cursor.fetchall()
for data in allData:
    print(data)

# 事物提交
conn.commit()
# 关闭数据库连接
conn.close()

验证输出

D:\anaconda3\python.exe "D:/PyCharm 2018.3.4/project/stockAnalysis/mainScript.py"
database version : PostgreSQL 10.12, compiled by Visual C++ build 1800, 32-bit 
(1, 'Shawn.li', 100)
(6, 'Shawn.li', 100)
(7, 'Shawn.li', 100)
(8, 'Shawn.wong', 100)
(9, 'Shawn.wong', 100)
(10, 'Shawn.zhang', 100)
(5, 'Sean.li', 100)
after update :
(1, 'Shawn.li', 100)
(6, 'Shawn.li', 100)
(7, 'Shawn.li', 100)
(8, 'Shawn.wong', 100)
(9, 'Shawn.wong', 100)
(10, 'Shawn.zhang', 100)
(5, 'Sean.li', 100)
after delete :
(6, 'Shawn.li', 100)
(7, 'Shawn.li', 100)
(8, 'Shawn.wong', 100)
(9, 'Shawn.wong', 100)
(10, 'Shawn.zhang', 100)
(5, 'Sean.li', 100)

Process finished with exit code 0