Ubuntu系統中Python調用C++程式 查詢Postgresql資料庫

"""
python 用時11.39秒，C++用時0.016秒，python調用C++程式3.07秒
"""
程式一：用python 查詢特定資料  注： finance_analysis資料表中記錄總數為51,754,932
# -*- coding:utf-8 -*-
# 先要建立postgresql資料庫連結
engine_pg = create_engine("postgresql+psycopg2://使用者名:,密碼@端口:/資料庫名", client_encoding='utf8')
import pandas as pd
if __name__ == '__main__':
    t0 = time.time()
    query = "SELECT * from finance_analysis where ts_code \
    = '600028.SH' and item = 'total_revenue';"
    df = pd.read_sql(query, con=engine_pg)
    print(df)
    t1 = time.time()
    print('program run time: ', t1 - t0, 'seconds.')

    """
    運作結果：
          ts_code start_date  end_date  ...           item        values rank
    0   600028.SH   20020101  20020331  ...  total_revenue  6.299500e+10  1.0
    1   600028.SH   20020101  20020630  ...  total_revenue  1.406280e+11  1.0
    ..        ...        ...       ...  ...            ...           ...  ...
    76  600028.SH   20210101  20211231  ...  total_revenue  2.740884e+12  1.0
    77  600028.SH   20220101  20220331  ...  total_revenue  7.713860e+11  2.0
    78  600028.SH   20220101  20220630  ...  total_revenue  1.612126e+12  2.0
    
    [79 rows x 11 columns]
    program run time:  11.390803575515747 seconds.
    
    Process finished with exit code 0
    """
程式二： C++程式
//用python 調用c++程式查詢同樣資料
//先在Ubuntu查詢是否已安裝c++ postgresql依賴包 libpqxx，
//無需要安裝 sudo apt-get libpqxx-dev 
//c++檔案名 t5.cpp
#include <iostream>
#include <pqxx/pqxx>
#include <ctime>
#include <string>
using namespace std;
using namespace pqxx;

extern "C"{
    void select_tb(void){
        clock_t start = clock();
        pqxx::connection conn("dbname=資料庫名 user=postgres password=密碼 \
          hostaddr=IP位址 port=5432");
        pqxx::work w(conn);
        string query = "SELECT * from finance_analysis where ts_code = '600028.SH' and item = 'total_revenue';";
        cout << "query:" << query << endl;
        pqxx::result res = w.exec( query );
        w.commit();
        int num = 0;
        cout << "no ts_code  start_date  end_date  table  country  market  currency  remark  item  values  rank" << endl;
        for (int i = 0;i <res.size(); ++ i)
        {
            cout << i << " ";  //序号
            for (int j = 0; j < 11; ++j)
            {
                cout << res[i][j] << "  ";   //顯示内容
            }
            cout << "\n";     //換行
            num += 1;
        }
        clock_t end = clock();
        double programTimes = ((double) end -start) / CLOCKS_PER_SEC;
        cout << "program time: " << programTimes << "seconds.";
        cout << endl;
        cout << "data num :" << num << endl;
    }
}


// 在ubuntu系統生成so檔案，并将此檔案拷貝到python程式目錄下
$ g + + -fPIC - shared t5.cpp - lpqxx - lpq - o t5.so

程式三：python程式調用C++生成的so檔案
import ctypes
if __name__ == '__main__':
    t0 = time.time()
    cdll_wm = ctypes.cdll.LoadLibrary('./t5.so')
    cdll_wm.select_tb()
    t1 = time.time()
    print('program time :', t1 - t0, 'sec.')
    """
    運作結果：
    76 600028.SH  20220101  20220331  stock_income  CHINA  SH  rmb  fina_rank  total_revenue  771386000000  2  
    77 600028.SH  20210101  20211231  stock_income  CHINA  SH  rmb  fina_rank  total_revenue  2740884000000  1  
    78 600028.SH  20220101  20220630  stock_income  CHINA  SH  rmb  fina_rank  total_revenue  1612126000000  2  
    program time: 0.021224seconds.
    data num :79
    program time : 3.0763885974884033 sec.
    
    Process finished with exit code 0
    “”“
    """
    Pgabc 2022000016
    author : Pgabc
    www.wmdbsoft.com
    """