TDengine-PHP调PYTHON

作者：陈华平

1 简介

客户端和服务端两台虚拟机

• 客户端安装：PHP、PYTHON、TAOS-CLIENT
• 服务端安装：PYTHON、TAOS-SERVER

  2 基础配置

  2.1 设置主机名

  客户端：hostnamectl set-hostname taos-c

  服务端：hostnamectl set-hostname taos-s

  2.2 关闭防火墙

  [root@taos-c ~]# systemctl stop firewalld
  [root@taos-c ~]# systemctl disable firewalld
  

  [root@taos-s ~]# systemctl stop firewalld
  [root@taos-s ~]# systemctl disable firewalld
  

  2.3 禁用SELINUX

  [root@taos-c ~]# cat /etc/selinux/config 
  SELINUX=disabled
  

  [root@taos-s ~]# cat /etc/selinux/config 
  SELINUX=disabled
  

  2.4 设置ETC/HOSTS

  [root@taos-c ~]# cat /etc/hosts
  192.168.0.76 taos-s
  

  [root@taos-s ~]# cat /etc/hosts
  192.168.0.76 taos-s
  

  2.5 测试

  重启reboot

  [root@taos-c ~]# ping taos-s
  PING taos-s (192.168.0.76) 56(84) bytes of data.
  64 bytes from taos-s (192.168.0.76): icmp_seq=1 ttl=64 time=0.618 ms
  64 bytes from taos-s (192.168.0.76): icmp_seq=2 ttl=64 time=0.274 ms
  ^C
  --- taos-s ping statistics ---
  2 packets transmitted, 2 received, 0% packet loss, time 1002ms
  rtt min/avg/max/mdev = 0.274/0.446/0.618/0.172 ms
  

  [root@taos-s ~]# ping taos-s
  PING taos-s (192.168.0.76) 56(84) bytes of data.
  64 bytes from taos-s (192.168.0.76): icmp_seq=1 ttl=64 time=0.016 ms
  64 bytes from taos-s (192.168.0.76): icmp_seq=2 ttl=64 time=0.029 ms
  64 bytes from taos-s (192.168.0.76): icmp_seq=3 ttl=64 time=0.048 ms
  ^C
  --- taos-s ping statistics ---
  3 packets transmitted, 3 received, 0% packet loss, time 2000ms
  rtt min/avg/max/mdev = 0.016/0.031/0.048/0.013 ms
  

  3 安装PYTHON、PIP

  客户端和服务端均需要安装PYTHON2.6.3，PIP为PYTHON自带，具体参考：

  http://tjei-tju.club/index.php/2021/09/07/tao_install_centos/

  [root@taos-c Python-3.6.3]# python3 -V
  Python 3.6.3
  [root@taos-c Python-3.6.3]# pip3 -V
  pip 9.0.1 from /usr/local/python3/lib/python3.6/site-packages (python 3.6)
  

  [root@taos-s Python-3.6.3]# python3 -V
  Python 3.6.3
  [root@taos-s Python-3.6.3]# pip3 -V
  pip 9.0.1 from /usr/local/python3/lib/python3.6/site-packages (python 3.6)
  

  4 服务端

  具体参考：

  http://tjei-tju.club/index.php/2021/09/07/tao_install_centos/

  配置文件：

  [root@taos-s ~]# cat /etc/taos/taos.cfg 
  firstEp                   taos-s:6030
  fqdn                      taos-s
  

  5 客户端（连接器）

  5.1 TAOS客户端

  5.1.1 安装客户端

  具体参考：

  http://tjei-tju.club/index.php/2021/09/07/tao_install_centos/

  配置文件：

  [root@taos-c TDengine-client-2.0.20.13]# cat /etc/taos/taos.cfg 
  firstEp                   taos-s:6030
  fqdn                      taos-s
  

  5.1.2 安装连接器

  [root@taos-c TDengine-client-2.0.20.13]# pip3 install connector/python/
  

  5.1.3 测试

  将测试文件/root/TDengine-client-2.0.20.13/examples/python/read_example.py文件服务地址修改：

  conn = taos.connect(host="192.168.0.76", user="root", password="taosdata", config="/etc/taos")

  执行结果如下：

  [root@taos-c python]# python3 read_example.py
  1
  1
  1
  1
  1
  1
  1
  1
  1
  1
  (datetime.datetime(2019, 7, 1, 0, 0), 1, 0.20000000298023224, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 1), 3, 0.8999999761581421, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 2), 6, 1.0, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 3), 4, 0.6000000238418579, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 4), 2, 1.0, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 5), 5, 0.5, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 6), 5, 0.20000000298023224, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 7), 8, 0.800000011920929, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 8), 5, 0.20000000298023224, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 9), 2, 0.10000000149011612, 'hello')
  Another query method 
  (datetime.datetime(2019, 7, 1, 0, 0), 1, 0.20000000298023224, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 1), 3, 0.8999999761581421, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 2), 6, 1.0, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 3), 4, 0.6000000238418579, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 4), 2, 1.0, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 5), 5, 0.5, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 6), 5, 0.20000000298023224, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 7), 8, 0.800000011920929, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 8), 5, 0.20000000298023224, 'hello')
  (datetime.datetime(2019, 7, 1, 0, 9), 2, 0.10000000149011612, 'hello')
  

  服务端和客户端安装成功

  5.2 PHP

  参考：

  http://tjei-tju.club/index.php/2020/06/30/php_install_centos/

  6 测试

  6.1 exec

  6.1.1 PHP文件

  传入SQL语句，返回JSON格式

  <?php
  header('Content-type:text/json');
  
  //php调用python脚本可以用 system（），exec（）和passthru() 函数，本篇文章主要介绍exec 函数
  
  // 显示所有信息，默认显示 INFO_ALL
  #header("Content-type: text/html; charset=utf-8");
  $sql = 'select * from db.t';
  exec("python3 read.py '$sql'", $out, $res);
  var_dump($out);
  
  echo "gettype\n";
  echo gettype($out) . "\n";
  
  echo "foreach\n";
  foreach($out as $val){
  	$v = json_decode($val);
  	echo $v->a . "\n";
  }
  
  ?>
  

  6.1.2 PYTHON文件

  将查询结果组合成PYTHON格式返回

  """
  This is the sample code for TDengine python2 client.
  """
  import taos
  import sys
  import datetime
  import random
  import json
  
  class DateEncoder(json.JSONEncoder):  
      def default(self, obj):  
          if isinstance(obj, datetime.datetime):  
              return obj.strftime('%Y-%m-%d %H:%M:%S')  
          elif isinstance(obj, date):  
              return obj.strftime("%Y-%m-%d")  
          else:  
              return json.JSONEncoder.default(self, obj) 
  
  def exitProgram(conn):
      conn.close()
      sys.exit()
  
  if __name__ == '__main__':
      start_time = datetime.datetime(2019, 7, 1)
      time_interval = datetime.timedelta(seconds=60)
  
      # Connect to TDengine server.
      # 
      # parameters:
      # @host     : TDengine server IP address 
      # @user     : Username used to connect to TDengine server
      # @password : Password 
      # @database : Database to use when connecting to TDengine server
      # @config   : Configuration directory
      conn = taos.connect(host="192.168.0.76", user="root", password="taosdata", config="/etc/taos")
     
      # Generate a cursor object to run SQL commands
      c1 = conn.cursor()
      # Create a database named db
      try:
          c1.execute('create database if not exists db ')
      except Exception as err:
          conn.close()
          raise(err)
          
      # use database
      try:
          c1.execute('use db')
      except Exception as err:
          conn.close()
          raise(err)
  
  
      # create table
      try:
          c1.execute('create table if not exists t (ts timestamp, a int, b float, c binary(20))')
      except Exception as err:
          conn.close()
          raise(err)
  
      # insert data 
      for i in range(10):
          try:
             value = c1.execute("insert into t values ('%s', %d, %f, '%s')" % (start_time, random.randint(1,10), random.randint(1,10)/10.0, 'hello'))
             #if insert, value is the affected rows
             #print(value)
          except Exception as err:
              conn.close()
              raise(err)
          start_time += time_interval
  
      # query data and return data in the form of list
      try:
          #c1.execute('select * from db.t')
          c1.execute(sys.argv[1])
      except Exception as err:
          conn.close()
          raise(err)
  
      # Column names are in c1.description list
      cols = c1.description
      # Use fetchall to fetch data in a list
      data = c1.fetchall()
  
      for col in data:
          result = {}
          result['a'] = col[0]
          result['b'] = col[1]
          result['c'] = col[2]
          result['d'] = col[3]
          print(json.dumps(result,cls=DateEncoder))
          
      conn.close()
  

  6.1.3 结果

  [root@taos-c www]# php test.php 
  array(10) {
    [0]=>
    string(75) "{"a": "2019-07-01 00:00:00", "b": 6, "c": 0.4000000059604645, "d": "hello"}"
    [1]=>
    string(74) "{"a": "2019-07-01 00:01:00", "b": 7, "c": 0.699999988079071, "d": "hello"}"
    [2]=>
    string(77) "{"a": "2019-07-01 00:02:00", "b": 10, "c": 0.20000000298023224, "d": "hello"}"
    [3]=>
    string(75) "{"a": "2019-07-01 00:03:00", "b": 5, "c": 0.8999999761581421, "d": "hello"}"
    [4]=>
    string(76) "{"a": "2019-07-01 00:04:00", "b": 5, "c": 0.20000000298023224, "d": "hello"}"
    [5]=>
    string(76) "{"a": "2019-07-01 00:05:00", "b": 4, "c": 0.10000000149011612, "d": "hello"}"
    [6]=>
    string(60) "{"a": "2019-07-01 00:06:00", "b": 9, "c": 0.5, "d": "hello"}"
    [7]=>
    string(75) "{"a": "2019-07-01 00:07:00", "b": 5, "c": 0.4000000059604645, "d": "hello"}"
    [8]=>
    string(77) "{"a": "2019-07-01 00:08:00", "b": 10, "c": 0.10000000149011612, "d": "hello"}"
    [9]=>
    string(76) "{"a": "2019-07-01 00:09:00", "b": 4, "c": 0.20000000298023224, "d": "hello"}"
  }
  gettype
  array
  foreach
  2019-07-01 00:00:00
  2019-07-01 00:01:00
  2019-07-01 00:02:00
  2019-07-01 00:03:00
  2019-07-01 00:04:00
  2019-07-01 00:05:00
  2019-07-01 00:06:00
  2019-07-01 00:07:00
  2019-07-01 00:08:00
  2019-07-01 00:09:00