Python连接Doirs查询实战

最新推荐文章于 2024-06-28 20:57:37 发布

wangleigiser

最新推荐文章于 2024-06-28 20:57:37 发布

阅读量4.7k

点赞数 5

分类专栏： Doris 文章标签： python 开发语言

本文链接：https://blog.csdn.net/wangleigiser/article/details/125655460

版权

Doris 专栏收录该内容

38 篇文章 10 订阅

订阅专栏

问题背景

有客户提出来他们的客户端是Python的，需要使用Python语言查询Doris。

问题解决思路

Doris高度兼容MySQL协议，Java客户端目前使用MySQL JDBC Client查询Doris。
Python也有MySQL Client，因此建议使用PHP MySQL Client，下面进行实战。

代码实战

1. python环境安装

我服务默认为Python2.7 ,下面介绍下在2.7环境上PyMySQL的安装。

pip安装：python get-pip.py

[root@17a5da45700b python]# python --version
Python 2.7.5
#download pip
curl https://bootstrap.pypa.io/pip/2.7/get-pip.py --output get-pip.py
#install pip 
sudo python get-pip.py

PyMySQL安装

[root@17a5da45700b python]# pip install PyMySQL
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting PyMySQL
 Downloading PyMySQL-0.10.1-py2.py3-none-any.whl (47 kB)
   |################################| 47 kB 9.3 MB/s 
Installing collected packages: PyMySQL
Successfully installed PyMySQL-0.10.1

安装结果验证：pip list |grep PyMySQL

[root@17a5da45700b python]# pip list |grep PyMySQL
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
PyMySQL             0.10.1

2. 功能测试

创建Doirs用户。

create database php_doris;
CREATE USER 'bigdata'@'%' IDENTIFIED BY 'mypss'
GRANT ALL ON php_doris.*  TO 'bigdata'@'%';
GRANT SELECT_PRIV,LOAD_PRIV,ALTER_PRIV,CREATE_PRIV,DROP_PRIV on php_doris.* TO 'bigdata'@'%';

创建Doris表

CREATE TABLE test
 (
         id VARCHAR(32) DEFAULT '',
         user_name VARCHAR(32) DEFAULT '',
         member_list VARCHAR(32) DEFAULT ''
 )
 DUPLICATE KEY(id)
 DISTRIBUTED BY HASH(id) BUCKETS 10
 PROPERTIES("replication_num" = "1");

Doris数据插入：

INSERT INTO test VALUES ('1','alex','123');

通过pymysql查询Doirs代码编写。vim mysql.py ，输入如下内容到文件。

import pymysql.cursors
conn = pymysql.connect(host="127.0.0.1",port=9030, user="root", password="mypss", database="php_doris", charset='utf8')
mycursor = conn.cursor()
mycursor.execute("select * from test limit 1")
result = mycursor.fetchall()
for data in result:
  print(data)

执行python命令：python mysql.py ，查询Doris，运行结果如下：
```
[root@17a5da45700b python]# python mysql.py 
(u'1', u'alex', u'123')
```

如果Doris没有密码，则password变量为空即可。具体代码如下：

import pymysql.cursors
conn = pymysql.connect(host="127.0.0.1",port=9030, user="root", password="", database="ssb", charset='utf8')
mycursor = conn.cursor()
mycursor.execute("select * from lineorder_rt limit 1")
result = mycursor.fetchall()
for data in result:
  print(data)