连接数据库测试

本例中展示的为本地连接数据库，如远程连接，需要修改配置文件pg_hba.conf或postgresql.conf以完成远程连接数据库的配置。

连接数据库

以下Python代码显示如何连接到现有的数据库，并返回一个数据库对象。

# shell
vim connect.py

# python
#!/usr/bin/python3

import os
import sys
import psycopg2

def connectVastbase():
    #连接数据库
    conn = psycopg2.connect(database="postgres", user="vastbase2", password="Aa@123456", host="127.0.0.1", port="5432")
    print('connect successful!')

if __name__=='__main__':
    connectVastbase()

# shell
python3 connect.py

脚本成功执行输出如下：

[vastbase2@CentOS76-08 testcode]$ python3 connect.py
NOTICE:  Last successful login info
login time:2021-08-10 06:30:57
application name:vb_ctl
Ip address:localhost
method:Trust

Failed 0 times since last successful login

connect successful!

创建表

以下Python程序将用于在之前连接的数据库Vastbase G100中创建一个表：

# shell
vim createtbl.pl

# python
#!/usr/bin/python3

import os
import sys
import psycopg2

def connectVastbase():
    #连接数据库
    conn = psycopg2.connect(database="postgres", user="vastbase2", password="Aa@123456", host="127.0.0.1", port="25432")
    print('connect successful!')
    #创建表
    cursor = conn.cursor()
    cursor.execute("drop table if exists tb_test")
    cursor.execute("create table tb_test(id integer not null primary key, name text)")
    conn.commit()
    print('create table successful!')

if __name__=='__main__':
    connectVastbase()

# shell
python3 createtbl.py

执行上述程序后，它将在Vastbase G100中创建一张tb_test表：

[vastbase2@CentOS76-08 testcode]$ python3 createtbl.py
NOTICE:  Last successful login info
login time:2021-08-10 06:36:58
application name:gs_clean
Ip address:localhost
method:Trust

Failed 0 times since last successful login

connect successful!
create table successful!

INSERT操作

以下Python程序显示了如何在上述示例中创建的tb_test表中创建/插入记录：

# shell
vim insert.py

# perl
#!/usr/bin/python3

import os
import sys
import psycopg2

def connectVastbase():
    # 连接数据库
    conn = psycopg2.connect(database="postgres", user="vastbase2", password="Aa@123456", host="127.0.0.1", port="25432")
    print('connect successful!')
    # 插入数据
    cursor = conn.cursor()
    cursor.execute("insert into tb_test(id, name) values(1, 'Anna')")
    name = 'Jack'
    cursor.execute("insert into tb_test(id, name) values(%s, '%s')" %(2, name))
    conn.commit()
    print('insert successful!')
    conn.close()

if __name__=='__main__':
    connectVastbase()

# shell
python3 insert.py

执行以上程序后，它将在tb_test表中插入给定的记录：

[vastbase2@CentOS76-08 testcode]$ python3 insert.py
NOTICE:  Last successful login info
login time:2021-08-10 06:46:58
application name:gs_clean
Ip address:localhost
method:Trust

Failed 0 times since last successful login

connect successful!
insert successful!

SELECT操作

以下Python程序显示了如何从上述示例中创建的tb_test表中获取和显示记录：

# shell
vim select.py

# perl
#!/usr/bin/python3

import os
import sys
import psycopg2

def connectVastbase():
    # 连接数据库
    conn = psycopg2.connect(database="postgres", user="vastbase2", password="Aa@123456", host="127.0.0.1", port="25432")
    print('connect successful!')
    # 查询数据
    cursor = conn.cursor()
    cursor.execute("select * from tb_test")
    # fetchall
    records = cursor.fetchall()
    for record in records:
        print('fetchall: id = ' + str(record[0]) + ', name = ' + str(record[1]))
    # fetchone
    cursor.execute("select * from tb_test")
    record = cursor.fetchone()
    print('fetchone: id = ' + str(record[0]) + ', name = ' + str(record[1]))
    conn.close()

if __name__=='__main__':
    connectVastbase()

# shell
python3 select.py

脚本成功执行输出如下：

[vastbase2@CentOS76-08 testcode]$ python3 select.py
NOTICE:  Last successful login info
login time:2021-08-10 06:51:59
application name:gs_clean
Ip address:localhost
method:Trust

Failed 0 times since last successful login

connect successful!
fetchall: id = 1, name = Anna
fetchall: id = 2, name = Jack
fetchone: id = 1, name = Anna

UPDATE操作

Python代码显示了如何使用UPDATE语句来更新指定记录，然后从tb_test表中获取和显示更新的记录：

# shell
vim update.pl

# perl
#!/usr/bin/python3

import os
import sys
import psycopg2

def connectVastbase():
    #连接数据库
    conn = psycopg2.connect(database="postgres", user="vastbase2", password="Aa@123456", host="127.0.0.1", port="25432")
    print('connect successful!')
    cursor = conn.cursor()
    #更新数据
    cursor.execute("update tb_test set name = '%s' where id = %s" %('Elsa', 1))
    print('update successful!')
    conn.commit()
    cursor.execute("select * from tb_test")
    records = cursor.fetchall()
    for record in records:
        print('after update: id = ' + str(record[0]) + ', name = ' + str(record[1]))
    conn.close()

if __name__=='__main__':
    connectVastbase()

# shell
perl update.pl

脚本成功执行输出如下：

[vastbase2@CentOS76-08 testcode]$ python3 update.py
NOTICE:  Last successful login info
login time:2021-08-10 06:56:59
application name:gs_clean
Ip address:localhost
method:Trust

Failed 0 times since last successful login

connect successful!
update successful!
after update: id = 2, name = Jack
after update: id = 1, name = Elsa

DELETE操作

Python代码显示了如何使用DELETE语句删除指定记录，然后从tb_test表中获取并显示剩余的记录：

# shell
vim delete.pl

# perl
#!/usr/bin/python3

import os
import sys
import psycopg2

def connectVastbase():
    # 连接数据库
    conn = psycopg2.connect(database="postgres", user="vastbase2", password="Aa@123456", host="127.0.0.1", port="25432")
    print('connect successful!')
    cursor = conn.cursor()
    # 删除数据
    cursor.execute("delete from tb_test where id = 2")
    conn.commit()
    print('delete successful!')
    cursor.execute("select * from tb_test")
    records = cursor.fetchall()
    for record in records:
        print('after delete: id = ' + str(record[0]) + ', name = ' + str(record[1]))
    conn.close()

if __name__=='__main__':
    connectVastbase()

# shell
python3 delete.py

脚本成功执行输出如下：

[vastbase2@CentOS76-08 testcode]$ python3 delete.py
NOTICE:  Last successful login info
login time:2021-08-10 07:03:36
application name:
Ip address:localhost
method:SHA256

Failed 0 times since last successful login

connect successful!
delete successful!
after delete: id = 1, name = Elsa