python 操作数据库（mysql）

python操作数据库，可以有如下几种方式。

1.安装pymysql。（python的一个mysql的插件，意思就是这个服务本身就是通过python来进行安装的）

2.安装mysql-connector。（数据库服务，类似java种的mysql-connector，通过mysql连接工具，可以连接上远程的mysql服务器）

　　使用pip安装插件：python -m pip install mysql-connector

我们栗子使用第二种方式！

一个hello world:

import mysql.connector

mydb = mysql.connector.connect(
    host="127.0.0.1",
    user="root",
    password="123456",
    database="test"
)

# 这个mycursor有点类似 mysql的console执行完命令后的的缓冲区的存储的数据 通过这个mycursor对象 进行遍历输出
mycursor = mydb.cursor()
mycursor.execute("SHOW tables")
for x in mycursor:
    print(x)

#     插入操作
sql = "INSERT INTO customer (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
# 多条数据插入
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

for val in val:
    mycursor.execute(sql, val)
mydb.commit()

mydb.close

附录 custme表的DDL：

CREATE TABLE `mdratool_property` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `session_id` varchar(30) NOT NULL COMMENT '用户ID',
  `property` json NOT NULL COMMENT '运行的配置信息',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=213 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='mdratool运行的配置信息表';

从从上面的hello world栗子来看，python的这个connector与java原生的mysql-connector类似，都是通过cursor进行数据的操作。

简单的封装:

from numbers import Number

defalut_split = ","


class Entity(object):

    def __init__(self, table_name, param_str, splits=None):
        self.table_name = table_name
        if None == splits:
            splits = defalut_split

        params = str.split(param_str, splits)

        self.params = params

    # 插入单条数据
    def insert_sql(self):
        values = []
        for temp in self.params:
            values.append("%s")

        sql = "INSERT INTO %s (%s) VALUES (%s)" % (
            self.table_name, defalut_split.join(self.params), defalut_split.join(values))

        return sql

    def update_sql(self, changes, dicts):
        sql = "UPDATE %s SET " % (self.table_name)
        change_strs = []
        for temp in changes:
            change_str = "%s = '%s'" % (temp, changes[temp])
            change_strs.append(change_str)

        sql = sql + defalut_split.join(change_strs)

        if len(dicts) < 1:
            return sql

        sql = sql + " WHERE "
        conditions = []
        for temp in dicts:
            condition = "%s = '%s'" % (temp, dicts[temp])
            conditions.append(condition)
        i = 1
        for condition in conditions:
            sql = sql + condition
            if i < len(conditions):
                i += 1
                sql = sql + " AND "
        return sql

    def delete_sql(self, dicts):
        sql = "DELETE FROM %s WHERE " % (self.table_name)
        conditions = []
        for temp in dicts:
            condition = "%s = '%s'" % (temp, dicts[temp])
            conditions.append(condition)

        i = 1
        for condition in conditions:
            sql = sql + condition
            if i < len(conditions):
                i += 1
                sql = sql + " AND "
        return sql

    def select_sql(self, dicts=None):
        sql = "SELECT * FROM %s " % (self.table_name)
        if dicts == None:
            return sql
        sql = sql + " WHERE "
        conditions = []
        for temp in dicts:
            condition = "%s = '%s'" % (temp, dicts[temp])
            conditions.append(condition)
        i = 1
        for condition in conditions:
            sql = sql + condition
            if i < len(conditions):
                i += 1
                sql = sql + " AND "
        return sql

对封装的使用

from entity import Entity
import mysql.connector

mydb = mysql.connector.connect(
    host="192.168.60.161",
    user="root",
    password="sxw2019@NJ321",
    database="test"
)

if __name__ == "__main__":

    c = Entity(table_name="customer", param_str="id,name,address")
    sql = c.select_sql({"id": 23})

    mycursor = mydb.cursor()
    mycursor.execute(sql)
    datas = mycursor.fetchall()

    for data in datas:
        print(type(data))

        print(data)

        id, name, address = (data)
        print(name.decode())

    # 插入操作
    monica = ['31', 'monica', 'nanjing']
    mycursor.execute(c.insert_sql(), monica)
    mydb.commit()

    mydb.close()

这个简单的封装，简化了对SQL的构造，简单的进行数据传输就可以拼凑出一个SQL，当然这也是一个很简单的升级，首先是单表操作，其次是没办法做到复杂的SQL的语句的生成（仅仅是做了CRUD的简单的SQL生成）

高阶的探索：python的ORM框架