mysql与postgresql的语法以及python创建连接的差异

最近在进行数据库的升级，即弃用之前的主从mysql，更改为postgresql。然后笔者就开始各种搜索两者使用时的不同之处，再次做一个汇总。尚不完善，旦有纰漏，望不吝赐教。持续更新中....

对于两种数据库的特性比较，请参考  https://www.cnblogs.com/geekmao/p/8541817.html

---

一、使用python链接数据库

差异主要表现在：

    connect 方法的参数名 db vs. database，pwd vs. password；

    使用 PooledDb 创建连接池时也需要修改下参数名称；

    返回查询结果类型为字典，mysql是在创建连接时加入 cursor_class = pymysql.cursors.DictCursor，

postgresql是在创建游标时加入 cursor_factory=psycopg2.extras.RealDictCursor。

     ps: java使用JDBC连接时只需要修改下驱动即可。

mysql

0、安装数据库链接所需的包

        pip install PyMySQL

1、链接数据库的几种方法以及各种参数

# -*- coding: utf-8 -*-
"""链接数据库"""

import pymysql
from DBUtils.PooledDB import PooledDB

# 参数依次为 主机地址，用户，密码，数据库名称，端口号，编码信息
def connect_database(dict_get=False):
    """
    创建数据库链接
    :params: dict_get: 查询时返回数据类型，字典或是元组，默认为元组类型
    :return: 数据库的链接
    """
    # 表示返回类型为元组
    cursor_class = pymysql.cursors.Cursor
    if dict_get:
        # 表示返回类型为字典
        cursor_class = pymysql.cursors.DictCursor
    conn = pymysql.connect(host='192.168.0.1', user='test', pwd='password', 
                           db='database_name', port=1111, charset='utf8', 
                           cursor_class=cursor_class)
    cur = conn.cursor()
    return conn, cur


def connect_database():
    """
    创建连接池
    :return: 数据库的连接池
    """
    connection_pools = PooledDB(creator=pymysql, mincached=1, maxcached=3, 
                                maxconnections=3, host='192.168.0.1', user='test', 
                                passwd='password', db='database_name', port=1111, 
                                charset='utf-8')
    return connection_pools

postgresql

0、安装数据库链接所需的包

      pip install psycopg2

1、链接数据库

# -*- coding: utf-8 -*-
"""链接postgresql数据库"""
import psycopg2
import psycopg2.extras


def connect_database(dict_get=False):
    """
    创建单个数据库链接
    :params: dict_get: 查询时返回数据类型，字典或是元组，默认为元组类型
    :return: 数据库的链接
    """
    conn = psycopg2.connect(host='192.168.0.1', database='database_name', user="test", password="password",  port=1111)
    # 返回查询数据为元组
    cursor_class = None
    # 返回查询数据为字典
    if dict_get:
        cursor_class = psycopg2.extras.RealDictCursor
    cur = conn.cursor(cursor_factory=cursor_class)
    return conn, cur


def connect_database_online():
    """
    创建连接池
    :return:
    """
    connection_pools = PooledDB(creator=psycopg2, mincached=1, maxcached=3,
                                maxconnections=3, host='192.168.0.1', user='test',
                                password='password', database='database_name', port=1111)
    return connection_pools

---

二、sql语句的差异(无标记则前者为mysql语法)

0、单双引号的使用

    在postgresql中，双引号取别名之用，且表名不能使用 `table_name` 这种反单引号

SELECT ID, NAME "名字" FROM table_name WHERE NAME='Vince';

1、一些方法的改动

  判断为空：IFNULL  -->  COALESCE (其中参数完全一样)

  分割字符串：SUBSTRING_INDEX  -->  SPLIT_PART (其中参数完全一样)

  拼接字段：CONCAT(strA, strB)  --> strA||strB  or  CONCAT(strA, strB)

2、LIKE 与 正则

  LIKE：LIKE  -->  ~~，ILIKE  -->  ~*

postgresql正则相关

	区分大小写	不区分大小写
匹配正则	~	~*
不匹配正则	!~	!~*

3、时间的计算(被这语法惊艳到了)

# 当前时间加1年
SELECT now()::timestamp + '1 year';
# 当前时间加一个月
SELECT now()::timestamp + '1 month';
# 当前时间加一天
SELECT now()::timestamp + '1 day';
# 当前时间加一个小时
SELECT now()::timestamp + '1 hour';
# 当前时间加一分钟
SELECT now()::timestamp + '1 min';
# 加一秒钟
SELECT now()::timestamp + '1 sec';
# 加1年1月1天1时1分1秒
SELECT now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';
# 把col字段转换成天 然后相加
SELECT now()::timestamp + (col || ' day')::interval FROM table;
# 减去一年
SELECT now()::timestamp + '-1 year';

5、操作表时有唯一值约束

# postgresql 首先添加唯一键约束

# 插入冲突则更新

mysql: 

INSERT INTO table_name (id, name) VALUES (1, 'Paul'),(2, 'Dwyne') ON DUPLICATE KEY UPDATE name= EXCLUDED.name;

postgresql:

INSERT INTO table_name (id, name) VALUES (1, 'Paul'),(2, 'Dwyane') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

# 插入冲突无操作

mysql:

INSERT IGNORE INTO table_name (id, name) VALUES (3, 'Vince');

postgresql:

# 添加唯一键

alter table table_name add constraint unique_test_name unique(name);

INSERT INTO table_name (id, name) VALUES (9,’Kawii’) ON CONFLICT ON CONSTRAINT unique_test_name DO NOTHING;

 

---

参考文章

1、https://blog.csdn.net/zacry/article/details/42742509(时间计算)

2、https://blog.csdn.net/xingxiupaioxue/article/details/77510172(正则与like)

3、https://blog.csdn.net/qq_42774426/article/details/88876297(插入冲突)