python psycopg2_python库之psycopg2

psycopg2 库是 python 用来操作 postgreSQL 数据库的第三方库。使用时需要先进行安装。pip install psycopg2。

python 部分准备就绪，接下来我们先来看看 postgreSQL 的基础知识。

postgreSQL

安装

Window 下安装非常简单，到官方网站下载安装包，然后按照提示安装即可。安装过程中会要求设置一个密码，用户名为 postgres ，这也是 postgreSQL 的默认数据库。

安装完成后，找到 PSQL，打开如图

在 # 后输入命令进行操作。如果想直接从命令行中进行操作，需键入以下命令：psql -U postgres ，然后键入密码(好像不需要密码)，就和直接从 PSQL 打开一样了。如图

此时我们进入了 postgres 用户的 postgres 数据库。但如果在命令行中只输入 psql 则会报错，因为它会试图进入 xuhang 用户(电脑用户名)的 xuhang 数据库中，而现在并没有这个数据库。

创建数据库用户

CREATE USER xuhang WITH PASSWORD ‘password’;

创建用户数据库

CREATE DATABASE xuhang OWNER xuhang;

GRANT ALL PRIVILEGES ON DATABASE xuhang to xuhang;

然后将 xuhang 数据库的所有权限都赋予 xuhang 用户，否则 xuhang 用户只能登陆控制台，没有任何数据库操作权限。

创建了 xuhang 用户及数据库后，我们就可以直接键入 psql 进入 xuhang 用户的 xuhang 数据库了(这块有点拗口)。如图

以上只是做个示范，接下来我们删掉 xuhang 数据库和 xuhang 用户。

DROP DATABASE xuhang;

DROP USER xuhang;

下面的操作均使用默认用户 postgres(拥有较高权限)。

控制台命令

\l 列出所有数据库

\d 列出当前数据库的所有表格

\d [table_name] 列出某一张表格的结构

\c [database_name] 切换数据库

\c - [user_name] 切换用户

如图

数据库操作

# 创建新表

CREATE TABLE IF NOT EXISTS dictionary(english VARCHAR(30), chinese VARCHAR(80), times SMALLINT, in_new_words SMALLINT);

# 插入数据

INSERT INTO dictionary(english, chinese, times, in_new_words) VALUES('hello', '你好', 0, 0);

# 选择记录

SELECT * FROM dictionary WHERE english = 'hello';

# 更新数据

UPDATE dictionary SET times = 1 WHERE english = 'hello';

# 删除记录

DELETE FROM dictionary WHERE english = 'hello';

# 添加列

ALTER TABLE dictionary ADD extra VARCHAR(40);

#更新结构

ALTER TABLE dictionary ALTER COLUMN english SET NOT NULL;

# 更名列

ALTER TABLE dictionary RENAME COLUMN extra TO extra_data;

# 删除列

ALTER TABLE dictionary DROP COLUMN extra_data;

# 表格更名

ALTER TABLE dictionary RENAME TO cidian;

# 删除表格

DROP TABLE IF EXISTS cidian;

如图

python操作postgreSQL

连接数据库

def connect_db():

try:

conn = psycopg2.connect(database='postgres', user='postgres',

password='xuhang', host='127.0.0.1', port=5432)

except Exception as e:

error_logger.error(e)

else:

return conn

return None

关闭数据库

def close_db_connection(conn):

conn.commit()

conn.close()

python 操作 postgreSQL 时，当连接关闭后，程序对数据库的操作才会生效。所以我封装了这两个函数，放在每一次操作的开始和结束。

创建数据库

def create_db():

conn = connect_db()

if not conn:

return

cur = conn.cursor()

cur.execute(" CREATE TABLE IF NOT EXISTS dictionary(english VARCHAR(30), "

"chinese VARCHAR(80), times SMALLINT, in_new_words SMALLINT)")

close_db_connection(conn)

psycopg2 提供了一个cursor类，用来在数据库 Session 里执行 PostgreSQL 命令。cursor对象由connection.cursor()方法创建：上面代码中的cur = conn.cursor()便创建了一个 cursor 对象。

执行 SQL 命令后的返回结果由cur.fetchall()接收为一个元组的列表。例如，查询 'hello' 的返回结果为 [('hello', 'int.喂，你好', 5, 0)] 。

其它操作

def init_db(file_name='dictionary.txt'):

conn = connect_db()

cur = conn.cursor()

try:

with open(file_name, 'r') as f:

for line in f:

line = line.strip()

words = line.split(' ')

for i, word in enumerate(words):

words[i] = deal_word(word)

info_logger.info("INSERT INTO dictionary(english, chinese, times, in_new_words) "

"VALUES(%s, %s, 0, 0)" % (words[0], words[1]))

cur.execute("INSERT INTO dictionary(english, chinese, times, in_new_words) "

"VALUES(%s, %s, 0, 0)" % (words[0], words[1]))

except IOError as e:

error_logger.error(e)

error_logger.error("initialize database failed!!!")

close_db_connection(conn)

else:

info_logger.info("initialize database dictionary completely...")

close_db_connection(conn)

我用一个 txt 文件里的数据初始化 dictionary 表。这里遇到了一个小问题，o'clock 插入时会出错，没错，就是那个 ' 惹的祸，解决方案为将字符串中单引号替换为两个单引号。

def deal_word(word):

word = word.replace("'", "''")

word = "'" + word + "'"

return word

把需插入的数据先经过deal_word()函数处理后再插入就不会出错了。

def add_item(english, chinese):

word = deal_word(english)

chinese = deal_word(chinese)

conn = connect_db()

cur = conn.cursor()

cur.execute("INSERT INTO dictionary(english, chinese, times, in_new_words) "

"VALUES(%s, %s, 0, 0)" % (word, chinese))

close_db_connection(conn)

向 dictionary 中添加一项数据。

结果

我用 PyQt5 做了一个小界面，以使我的词典便于使用(虽然很丑)，但本来也只是学学 python 操作 postgreSQL 的基本用法而已，所以，原谅我的审美🤦‍。。

说说我的思路：

查询单词后，可以选择将该词加入生词本，方便日后单独学习。当你查询一个单词超过5遍时，会提醒你将该词加入生词本。当你把一个单词从生词本中移除时，会显示恭喜你记住了这个单词。另外，除了数据库里的单词，还可以自定义加入自己想记忆的东西。

如图：

最后这张图，瞎说什么大实话。