本文介绍通过python访问GBase 8a的方法。涉及连接，DDL,DML,DQL，存储过程，加载等常见功能。
目录导航
版本
python区分2和3， 所以驱动也区分。本文介绍以python2为基础，样例也是py2的格式
gbase-connector-python2-5.0.1.tar.gz
安装
解压缩
[gbase@vm246 python]$ ll
total 44
-rw-r--r--. 1 root root 43827 Sep 20 08:51 gbase-connector-python2-5.0.1.tar.gz
[gbase@vm246 python]$ tar xvf gbase-connector-python2-5.0.1.tar.gz
gbase-connector-python2-5.0.1/
gbase-connector-python2-5.0.1/GBaseConnector/
gbase-connector-python2-5.0.1/GBaseConnector/errmsg/
gbase-connector-python2-5.0.1/GBaseConnector/errmsg/eng/
gbase-connector-python2-5.0.1/GBaseConnector/errmsg/eng/client_errmsg.py
gbase-connector-python2-5.0.1/GBaseConnector/errmsg/eng/__init__.py
gbase-connector-python2-5.0.1/GBaseConnector/errmsg/__init__.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseConnection.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseConstants.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseCursor.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseError.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseErrorCode.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseLogger.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseProtocol.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseSocket.py
gbase-connector-python2-5.0.1/GBaseConnector/GBaseUtils.py
gbase-connector-python2-5.0.1/GBaseConnector/__init__.py
gbase-connector-python2-5.0.1/PKG-INFO
gbase-connector-python2-5.0.1/README
gbase-connector-python2-5.0.1/setup.py
[gbase@vm246 python]$安装(Build+Install)
[root@vm246 gbase-connector-python2-5.0.1]$ python setup.py build
running build
running build_py
creating build
creating build/lib
creating build/lib/GBaseConnector
copying GBaseConnector/GBaseConnection.py -> build/lib/GBaseConnector
copying GBaseConnector/GBaseConstants.py -> build/lib/GBaseConnector
copying GBaseConnector/GBaseCursor.py -> build/lib/GBaseConnector
copying GBaseConnector/GBaseError.py -> build/lib/GBaseConnector
copying GBaseConnector/GBaseErrorCode.py -> build/lib/GBaseConnector
copying GBaseConnector/GBaseLogger.py -> build/lib/GBaseConnector
copying GBaseConnector/GBaseProtocol.py -> build/lib/GBaseConnector
copying GBaseConnector/GBaseSocket.py -> build/lib/GBaseConnector
copying GBaseConnector/GBaseUtils.py -> build/lib/GBaseConnector
copying GBaseConnector/__init__.py -> build/lib/GBaseConnector
creating build/lib/GBaseConnector/errmsg
copying GBaseConnector/errmsg/__init__.py -> build/lib/GBaseConnector/errmsg
creating build/lib/GBaseConnector/errmsg/eng
copying GBaseConnector/errmsg/eng/__init__.py -> build/lib/GBaseConnector/errmsg/eng
copying GBaseConnector/errmsg/eng/client_errmsg.py -> build/lib/GBaseConnector/errmsg/eng
[root@vm246 gbase-connector-python2-5.0.1]# python setup.py install
running install
running build
running build_py
running install_lib
creating /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseConnection.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseConstants.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseCursor.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseError.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseErrorCode.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseLogger.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseProtocol.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseSocket.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/GBaseUtils.py -> /usr/lib/python2.7/site-packages/GBaseConnector
copying build/lib/GBaseConnector/__init__.py -> /usr/lib/python2.7/site-packages/GBaseConnector
creating /usr/lib/python2.7/site-packages/GBaseConnector/errmsg
copying build/lib/GBaseConnector/errmsg/__init__.py -> /usr/lib/python2.7/site-packages/GBaseConnector/errmsg
creating /usr/lib/python2.7/site-packages/GBaseConnector/errmsg/eng
copying build/lib/GBaseConnector/errmsg/eng/__init__.py -> /usr/lib/python2.7/site-packages/GBaseConnector/errmsg/eng
copying build/lib/GBaseConnector/errmsg/eng/client_errmsg.py -> /usr/lib/python2.7/site-packages/GBaseConnector/errmsg/eng
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseConnection.py to GBaseConnection.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseConstants.py to GBaseConstants.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseCursor.py to GBaseCursor.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseError.py to GBaseError.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseErrorCode.py to GBaseErrorCode.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseLogger.py to GBaseLogger.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseProtocol.py to GBaseProtocol.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseSocket.py to GBaseSocket.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/GBaseUtils.py to GBaseUtils.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/errmsg/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/errmsg/eng/__init__.py to __init__.pyc
byte-compiling /usr/lib/python2.7/site-packages/GBaseConnector/errmsg/eng/client_errmsg.py to client_errmsg.pyc
running install_egg_info
Writing /usr/lib/python2.7/site-packages/gbase_connector_python-5.0.1-py2.7.egg-info
[root@vm246 gbase-connector-python2-5.0.1]#连接数据库
[root@vm246 python]# cat testCon.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'PASSWORD_OF_ROOT',
'connection_timeout':3600,
'charset':'utf8'}
try:
conn = connect()
conn.connect(**config)
print conn
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[root@vm246 python]#
[root@vm246 python]# python testCon.py
<GBaseConnector.GBaseConnection.GBaseConnection object at 0x7ff39b92d990>DDL
[gbase@vm246 python]$ cat testDDL.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
print cur.execute("CREATE table if not exists testPython(id int)")
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[gbase@vm246 python]$
gbase> use testdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> desc testPython;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.01)
gbase>DML
[gbase@vm246 python]$ cat testDML.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
print cur.execute("CREATE table if not exists testPython(id int)")
print cur.execute("insert into testPython values(12345678)")
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[gbase@vm246 python]$ python testDML.py
None
None
[gbase@vm246 python]$ gccli -pAdmin2O11O531 testdb
GBase client 9.5.3.29.23fba856. Copyright (c) 2004-2023, GBase. All Rights Reserved.
gbase> select * from testPython;
+----------+
| id |
+----------+
| 12345678 |
+----------+
1 row in set (Elapsed: 00:00:00.01)DQL 单次
[root@vm246 python]# cat testDQL.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
cur.execute("drop table if exists testPython")
cur.execute("CREATE table if not exists testPython(id int)")
cur.execute("insert into testPython values(12345678)")
cur.execute("select * from testPython")
row = cur.fetchone()
print row
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[root@vm246 python]# python testDQL.py
(12345678,)
[root@vm246 python]#DQL 多次
前一个select必须全部读取完毕，否则同一个连接会报错 Unread result found.
[root@vm246 python]# cat testDQL.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
cur.execute("drop table if exists testPython")
cur.execute("CREATE table if not exists testPython(id int)")
cur.execute("insert into testPython values(12345678)")
cur.execute("select * from testPython")
row = cur.fetchall()
print row
cur.execute("insert into testPython values(22222),(33333)")
cur.execute("select * from testPython")
row2 = cur.fetchmany(2)
print row2
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[root@vm246 python]# python testDQL.py
[(12345678,)]
[(12345678,), (22222,)]
[root@vm246 python]#一次执行多个SQL
使用multi_stmt=True 参数，否则会报错 Use query_iter for statements with multiple queries.
DDL,DML返回的是None
DQL 返回结果集
DDL/DML
[root@vm246 python]# cat testMultiSQL.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
cur.execute("drop table if exists testPython;CREATE table if not exists testPython(id int);insert into testPython values(12345678)", multi_stmt=True)
cur.execute("select * from testPython")
row = cur.fetchall()
print row
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[root@vm246 python]# python testMultiSQL.py
[(12345678,), (22222,), (33333,)]
DQL
[root@vm246 python]# cat testMultiDQLSQL.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
cur.execute("drop table if exists testPython")
cur.execute("CREATE table if not exists testPython(id int)")
cur.execute("insert into testPython values(12345678)")
rss = cur.execute("select * from testPython;select count(*) from testPython", multi_stmt=True)
for rs in rss:
print rs.fetchall()
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[root@vm246 python]# python testMultiDQLSQL.py
[(12345678,)]
[(1,)]
[root@vm246 python]#批量执行Batch
[root@vm246 python]# cat testPrepared.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
cur.execute("drop table if exists testPython2")
cur.execute("CREATE table if not exists testPython2(id int,name varchar(100))")
opfmt = "INSERT INTO testPython2(id, name) VALUES(%s, %s)"
rows = []
for i in xrange(0, 100):
rows.append((i, "row" + str(i)))
cur.executemany(opfmt, rows)
cur.execute("select * from testPython2")
row = cur.fetchall()
print row
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[root@vm246 python]# python testPrepared.py
[(0, u'row0'), (1, u'row1'), (2, u'row2'), (3, u'row3'), (4, u'row4'), (5, u'row5'), (6, u'row6'), (7, u'row7'), (8, u'row8'), (9, u'row9'), (10, u'row10'), (11, u'row11'), (12, u'row12'), (13, u'row13'), (14, u'row14'), (15, u'row15'), (16, u'row16'), (17, u'row17'), (18, u'row18'), (19, u'row19'), (20, u'row20'), (21, u'row21'), (22, u'row22'), (23, u'row23'), (24, u'row24'), (25, u'row25'), (26, u'row26'), (27, u'row27'), (28, u'row28'), (29, u'row29'), (30, u'row30'), (31, u'row31'), (32, u'row32'), (33, u'row33'), (34, u'row34'), (35, u'row35'), (36, u'row36'), (37, u'row37'), (38, u'row38'), (39, u'row39'), (40, u'row40'), (41, u'row41'), (42, u'row42'), (43, u'row43'), (44, u'row44'), (45, u'row45'), (46, u'row46'), (47, u'row47'), (48, u'row48'), (49, u'row49'), (50, u'row50'), (51, u'row51'), (52, u'row52'), (53, u'row53'), (54, u'row54'), (55, u'row55'), (56, u'row56'), (57, u'row57'), (58, u'row58'), (59, u'row59'), (60, u'row60'), (61, u'row61'), (62, u'row62'), (63, u'row63'), (64, u'row64'), (65, u'row65'), (66, u'row66'), (67, u'row67'), (68, u'row68'), (69, u'row69'), (70, u'row70'), (71, u'row71'), (72, u'row72'), (73, u'row73'), (74, u'row74'), (75, u'row75'), (76, u'row76'), (77, u'row77'), (78, u'row78'), (79, u'row79'), (80, u'row80'), (81, u'row81'), (82, u'row82'), (83, u'row83'), (84, u'row84'), (85, u'row85'), (86, u'row86'), (87, u'row87'), (88, u'row88'), (89, u'row89'), (90, u'row90'), (91, u'row91'), (92, u'row92'), (93, u'row93'), (94, u'row94'), (95, u'row95'), (96, u'row96'), (97, u'row97'), (98, u'row98'), (99, u'row99')]
[root@vm246 python]#存储过程
[root@vm246 python]# cat testProcedure.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
cur.execute("drop procedure if exists testPython_proc")
cur.execute("create procedure testPython_proc() \
begin \
select 1+2;\
select now();\
end")
rss = cur.callproc("testPython_proc")
for rs in rss:
print rs.fetchall()
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[root@vm246 python]# python testProcedure.py
[(3,)]
[(datetime.datetime(2023, 9, 22, 9, 30, 31),)]
[root@vm246 python]#
加载
加载成功行数可以直接用rowcount属性。而忽略行数我测试的版本尚不支持，所以用info做了自行解析来实现的。
[root@vm246 python]# cat testLoad.py
from GBaseConnector import connect,GBaseError
if __name__ == '__main__':
config = {'host':'10.10.10.10',
'port':5258,
'database':'testdb',
'user':'root',
'passwd':'Admin2O11O531'}
try:
conn = connect()
conn.connect(**config)
cur = conn.cursor()
cur.execute("drop table if exists testPython2")
cur.execute("CREATE table if not exists testPython2(id int,name varchar(100))")
cur.execute("load data infile 'file://10.10.10.10/home/gbase/py.txt' into table testpython2 fields terminated by ','")
print cur.info
print("Loaded",cur.rowcount)
infors=cur.info.split()
print("TaskId",infors[1])
print("Skipped",infors[7])
except GBaseError.DatabaseError, err:
print err
finally:
conn.close()
[root@vm246 python]# python testLoad.py
Task 3237 finished, Loaded 1 records, Skipped 0 records
('Loaded', 1)
('TaskId', '3237')
('Skipped', '0')
[root@vm246 python]#