Python 操作 mysql 数据库笔记

1. 导入 mysql 库

import mysql.connector

2.  创建 mysql 链接，如下函数，作为一个类的初始化函数，实例化对象时，传进一个数据库名作为参数。

def __init__(self, db_name):
    """连接数据库"""
    self.mOperator = mysql.connector.connect(
        host='localhost',
        user='root',
        passwd='xxx990230.',
    )
    self.mCursor = self.mOperator.cursor()  # 获得光标
    self.select_db(db_name)        # 选择数据库，函数实现在下面

3. 选择数据库，如下函数，调用时传进数据库名作为参数

def select_db(self, database_name):
    """选择数据库"""
    select_db_sql = 'use ' + database_name
    try:
        self.mCursor.execute(select_db_sql)
    except:
        print('selet_db except')
        return

4. 插入数据，如下函数，调用时传入插入sql语句和val

def insert_data(self, insert_sql, value_sql):
    ''' 插入数据 '''
    if insert_sql and value_sql:
        try:
            self.mCursor.execute(insert_sql, value_sql)
            self.mOperator.commit()
        except Exception as err:
            print('insert_date except: {}'.format(err))
            return


if __name__ == '__main__':
# 注意，不管是什么数据类库，占位符都只能用 %s
insert_sql = "INSERT INTO my_test_table_1(" \
                 "identity_id," \
                 "name," \
                 "age," \
                 "male)" \
                 "value" \
                 "(%s, %s, %s, %s)"

value_sql = ('44511119999002207504', '林小林', '30', '男')

# 调用
insert_data(insert_sql, value_sql)

5. 创建数据表

def create_tbl(self, tbl_name='build_modem_time_tbl'):
    ''' 创建数据表,形参为各列名 '''
    create_tbl_sql = "CREATE TABLE IF NOT EXISTS my_test_table_1(" \
                 "identity_id VARCHAR(32) PRIMARY KEY, " \
                 "name VARCHAR(16), " \
                 "age VARCHAR(32), " \
                 "male VARCHAR(32))"
    try:
        self.mCursor.execute(create_tbl_sql)
    except:
        print('create_tbl except')
        return

6. 查询数据

def query_data(self, select_sql):
    ''' 查询数据 '''
    if select_sql:
        try:
            self.mCursor.execute(select_sql)
            query_result = self.mCursor.fetchall()
            return query_result
        except:
            print('query_date except')
            return

if __name__ == '__main__':
# sql 语句
select_sql = "SELECT * FROM my_test_table_1 WHERE name = '林小林'"
# 调用
query_data(select_sql)

7. 更新数据

def update_db(self, update_sql):
    '''更新数据'''
    try:
        self.mCursor.execute(update_sql)
        self.mOperator.commit()
    except Exception as err :
        print('update exception: {}'.format(err))

8. 创建数据库

def create_db(self, db_name='build_time'):
    """创建数据库"""
    create_db_sql = 'CREATE DATABASE ' + db_name
    try:
        self.mCursor.execute(create_db_sql)
    except:
        print('create_db except')
        return