Python连接Doirs查询实战

问题背景

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

问题解决思路

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

代码实战

1. python环境安装

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

  1. 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 
    
  2. 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
  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. 功能测试
  1. 创建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'@'%';
    
  2. 创建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");
     
    
  3. Doris数据插入:

    INSERT INTO test VALUES ('1','alex','123');
    
  4. 通过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)
    
  5. 执行python命令:python mysql.py ,查询Doris,运行结果如下:

    [root@17a5da45700b python]# python mysql.py 
    (u'1', u'alex', u'123')
    
  6. 如果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)
    
  • 5
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值