原作者：陆凯

• 实验环境
• 研究背景
• 准备工作
• 实验过程
• 总结

实验环境

操作系统：centos7
数据库版本：MogDB 3.0.3
python版本：2.7.5

研究背景

Python简洁易用、生态强大，作为一种流行的编程语言，以及其在数据处理和分析领域的广泛应用，越来越多的开发任务由Python主导开发，如何对接数据库是极其重要的一环，本篇文章分析了如何使用Python的驱动包连接到MogDB,并执行DML\DQL\DDL等操作。

准备工作

Python环境：可以通过yum、apt-get、二进制编译等方式安装
数据库：MogDB

实验过程

linux一般默认预安装python，可以通过python -V查看版本，或者通过在/usr/bin下设置软连接指定到其他的版本

[root@c2 ~]# python -V
Python 2.7.5

可以通过调用Python的pip工具下载安装psycopg2包，如果在内网中，需要离线下载psycopg2包（访问https://pypi.org/ 搜索psycopg2，下载后解压到python目录下的Lib/site-packages中，进入psycopg2目录，执行安装python install setup.py）

[root@c1 bin]# python -m pip install psycopg2 -i http://mirrors.aliyun.com/pypi/simple/ --trusted-host mirrors.aliyun.com
WARNING: Running pip install with root privileges is generally not a good idea. Try `__main__.py install --user` instead.
Collecting psycopg2
  Downloading http://mirrors.aliyun.com/pypi/packages/f7/fa/6e6bb7a7bbe4e02b35aa2fc009fb53221663a5e07e333b72cb5a85e4dbb0/psycopg2-2.9.7.tar.gz (383kB)
    100% |████████████████████████████████| 389kB 965kB/s 
    Complete output from command python setup.py egg_info:
    running egg_info
    creating pip-egg-info/psycopg2.egg-info
    writing pip-egg-info/psycopg2.egg-info/PKG-INFO
    writing dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txt
    writing top-level names to pip-egg-info/psycopg2.egg-info/top_level.txt
    writing manifest file 'pip-egg-info/psycopg2.egg-info/SOURCES.txt'
    
    Error: pg_config executable not found.
    
    pg_config is required to build psycopg2 from source.  Please add the directory
    containing pg_config to the $PATH or specify the full executable path with the
    option:
    
        python setup.py build_ext --pg-config /path/to/pg_config build ...
    
    or with the pg_config option in 'setup.cfg'.
    
    If you prefer to avoid building psycopg2 from source, please install the PyPI
    'psycopg2-binary' package instead.
    
    For further information please check the 'doc/src/install.rst' file (also at
    <https://www.psycopg.org/docs/install.html>).
    
    
    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-udzy5w9c/psycopg2/

上面的报错信息通常是由于缺少所需的构建工具或依赖项导致的。在处理psycopg2的安装过程中，可能会遇到一些编译错误或依赖项缺失的问题，可以通过下面的方式修复：

yum install -y libpq-dev python-dev*
python -m pip install --upgrade pip setuptools

再次执行在线安装：

python -m pip install psycopg2 -i http://mirrors.aliyun.com/pypi/simple/ --trusted-host mirrors.aliyun.com

编写a.py（create table、insert、delete、update），需要在头部导入psycopg2包，通过该包的connect()方法建立远程连接，正确填入远程数据库的ip、port、username、password、databasename等信息，并确保网络连通、数据库端监听设置正确、白名单放开等。

vi a.py
#!/usr/bin/python3

#!/usr/bin/python3

import psycopg2

conn = psycopg2.connect(database="postgres", user="lk", password="panweidb@123", host="192.168.2.128", port="17700")

cur = conn.cursor()
cur.execute('''CREATE TABLE public.lukai
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')

cur.execute('''insert into public.lukai values(1,'zhangfei',22,'beijing',5000);''')
cur.execute('''insert into public.lukai values(2,'guanyu',22,'beijing',6000);''')
cur.execute('''insert into public.lukai values(3,'liubei',22,'beijing',8000);''')
cur.execute('''delete from public.lukai where name='guanyu' ;''')
cur.execute('''update public.lukai set SALARY=SALARY+1500 where name='liubei';''')

conn.commit()
conn.close()

执行脚本

python a.py 

服务端查看结果

[omm@c1 ~]$ gsql -r
gsql ((MogDB(openGauss) 1.0.0 build 9a7e96bc) compiled at 2022-10-15 20:54:36 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# \dt
                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 public | lukai | table | lk    | {orientation=row,compression=no}
(1 row)

MogDB=# \d lukai
        Table "public.lukai"
 Column  |     Type      | Modifiers 
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) | 
 salary  | real          | 
Indexes:
    "lukai_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

MogDB=# select * from lukai;
 id |   name   | age |                      address                       | salary 
----+----------+-----+----------------------------------------------------+--------
  1 | zhangfei |  22 | beijing                                            |   5000
  3 | liubei   |  22 | beijing                                            |   9500
(2 rows)

编写b.py（select、drop）

vi b.py
#!/usr/bin/python3

import psycopg2

conn = psycopg2.connect(database="postgres", user="lk", password="panweidb@123", host="192.168.2.128", port="17700")

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary  from lukai;")
rows = cur.fetchall()
#for row in rows:
#   print("ID = ", row[0])
#   print("NAME = ", row[1])
#   print("ADDRESS = ", row[2])
#   print("SALARY = ", row[3])

for row in rows:
    print(row)

cur.execute("drop table lukai;")

conn.commit()
conn.close()

执行脚本

[root@c1 bin]# python b.py 
(1, 'zhangfei', 'beijing                                           ', 5000.0)
(3, 'liubei', 'beijing                                           ', 9500.0)

服务端查看结果

MogDB=# \d lukai
Did not find any relation named "lukai".

总结

Python连接到MogDB不仅仅只有这一种方式，还可通过pyodbc等包来连接，抓住其灵活多样的特点我们可以尝试多种连接方式方法。