[223]python操作MySQL数据库

周小董

已于 2022-12-01 18:58:08 修改

阅读量793

点赞数

分类专栏：数据库&中间件文章标签：数据库

于 2018-07-03 09:03:59 首次发布

本文链接：https://blog.csdn.net/xc_zhou/article/details/80893221

版权

数据库&中间件专栏收录该内容

123 篇文章 12 订阅

订阅专栏

文章目录

python DB API cursor 常用接口

description
如果 cursor 执行了查询的 sql 代码。那么读取 cursor.description 属性的时候，将返回一个列表，这个列表中装的是元组，元组中装的分别
是 (name,type_code,display_size,internal_size,precision,scale,null_ok) ，其中 name 代表的是查找出来的数据的字段名称，其他参数暂时用处不大。
rowcount
代表的是在执行了 sql 语句后受影响的行数。
close
关闭游标。关闭游标以后就再也不能使用了，否则会抛出异常。
execute(sql[,parameters])
执行某个 sql 语句。如果在执行 sql 语句的时候还需要传递参数，那么可以传给 parameters 参数。示例代码如下：

cursor.execute("select * from article where id=%s",(1,))

fetchone
在执行了查询操作以后，获取第一条数据。
fetchmany(size)
在执行查询操作以后，获取多条数据。具体是多少条要看传的 size 参数。如果不传 size 参数，那么默认是获取第一条数据。
fetchall
获取所有满足 sql 语句的数据。

MySQL 创建数据表

创建MySQL数据表需要以下信息：

表名
表字段名
定义每个表字段

语法

以下为创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在test数据库中创建数据表table_test：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- DROP TABLE IF EXISTS `table_test`;
CREATE TABLE IF NOT EXISTS table_test(
  `id` int(12) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `company_id` varchar(32) NULL DEFAULT NULL COMMENT 'company_id',
  `` varchar(128) NULL DEFAULT NULL COMMENT '公司名称',
  `` int(6) NULL DEFAULT 0 COMMENT '是否高新技术企业 0=否 1=是',
  `` date NULL DEFAULT NULL COMMENT	'发行日期',
  ``	decimal(15, 2) NULL DEFAULT NULL COMMENT'计划发行规模',
  `` LONGTEXT NULL DEFAULT NULL COMMENT '相同发行人',
  `create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'sql创建时间',
  `update_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT 'sql更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

实例解析：

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE设置存储引擎，CHARSET设置编码。

通过命令提示符创建表

通过 mysql> 命令窗口可以很简单的创建MySQL数据表。你可以使用 SQL 语句CREATE TABLE来创建数据表。

实例

以下为创建数据表runoob_tbl实例:

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_tbl(
   -> runoob_id INT NOT NULL AUTO_INCREMENT,
   -> runoob_title VARCHAR(100) NOT NULL,
   -> runoob_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( runoob_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>

注意：MySQL命令终止符为分号;。

创建 MySql 的表时，表名和字段名外面的符号 ` 不是单引号，而是英文输入法状态下的反单引号，也就是键盘左上角 esc 按键下面的那一个 ~ 按键，坑惨了。
反引号是为了区分 MySql 关键字与普通字符而引入的符号，一般的，表名与字段名都使用反引号。

数据插入

在MYSQL中，运行INSERT INTO 插入语句中的字段是否一定要把该表的字段全部填上?

例如表tab_name有(col1,col2,col3，col4)4个字段。
只填你需要添的，就要把列名写上values后对应列值，
语法：insert into tab_name (col1,col2,col3) values(val1,val2,val3);

如果你想省略列名，那就要把所有字段的值都填上了，
语法：insert into tab_name values(val1,val2,val3,val4);

MySQL数据库，插入重复数据

sql = "INSERT INTO test_c(id,name,sex)values(%s,%s,%s)"  
param = (1,'AJ','MAN')  
n = cursor.execute(sql,param)  
db.commit()

当我们使用普通的 “INSERT INTO" 插入数据，如果数据有重复就会有报错：
提示的是键值重复

Traceback (most recent call last):  
  File "D:/python/tongbu_py/test.py", line 14, in <module>  
    n = cursor.execute(sql,param)  
  File "D:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute  
    self.errorhandler(self, exc, value)  
  File "D:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler  
    raise errorclass, errorvalue  
_mysql_exceptions.IntegrityError: (1062, "Duplicate entry '1-AJ-MAN' for key 'PRIMARY'")

我们可以使用另外2个传入方法： INSERT IGNORE INTO 和 REPLACE INTO：

insert ignore into

INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的

insert ignore into user_info (last_name,first_name) values ('LeBron','James');

replace into

REPLACE INTO 如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

replace into 有三种形式：

replace into tbl_name(col_name, ...) values(...)
replace into tbl_name(col_name, ...) select ...
replace into tbl_name set col_name=value, ...

前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

不存在插入，存在更新

INSERT 语句的一部分,如果指定 ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE，如果不会导致唯一值列重复的问题，则插入新行

sql 语句原型：

insert into table (player_id,award_type,num) values(20001,0,1) on DUPLICATE key update num=num+values(num)

python mysql操作

# -*- coding: utf-8 -*-
import MySQLdb
import configparser


class OperateMySQL:
    def __init__(self):
        # 从配置文件读取mysql配置
        # cp = ConfigParser(os.environ)  # 从环境变量读取
        cp = configparser.ConfigParser()  # 从ini配置文件读取
        cp.read('../config.ini', encoding='utf-8')

        host = cp.get('mysql', 'host')
        user = cp.get('mysql', 'user')
        passwd = cp.get('mysql', 'password')
        dbname = cp.get('mysql', 'dbname')
        self.db_conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=dbname, charset='utf8')

    def read_data(self, sql):
        cursor = self.db_conn.cursor()
        cursor.execute(sql)
        data = list(cursor)
        cursor.close()
        return data

    def insert_data(self, table_name, table_fields, values):
        fields_str = ','.join(table_fields)
        data_val = "(" + "%s," * (len(table_fields)) + ")"
        data_val = data_val.replace(',)', ')')
        cursor = self.db_conn.cursor()
        sql = 'insert into %s(%s) values %s' % (table_name, fields_str, data_val)
        cursor.execute(sql, values)
        cursor.close()

    def replace_data(self, table_name, table_fields, values):
        fields_str = ','.join(table_fields)
        data_val = "(" + "%s," * (len(table_fields)) + ")"
        data_val = data_val.replace(',)', ')')
        cursor = self.db_conn.cursor()
        sql = 'replace into %s(%s) values %s' % (table_name, fields_str, data_val)
        cursor.execute(sql, values)
        cursor.close()

    def insert_update_data(self, table_name, table_fields, field_values):
        fields_str = ','.join(table_fields)
        data_val = "(" + "%s," * (len(table_fields)) + ")"
        data_val = data_val.replace(',)', ')')
        cursor = self.db_conn.cursor()
        # 获取主键或者唯一索引列
        unique_sql = "show index from %s where non_unique = 0" % table_name
        cursor.execute(unique_sql)
        unique = cursor.fetchall()
        unique_list = [i[4] for i in unique]
        update_list = []
        for j in table_fields:
            if j not in unique_list:
                str = '%s = values(%s)' % (j, j)
                update_list.append(str)
        update_str = ','.join(update_list)
        sql = 'insert into %s(%s) values %s on duplicate key update %s' % (table_name, fields_str, data_val, update_str)
        cursor.execute(sql, field_values)
        cursor.close()

    def update_table(self, table_name, update_field, update_val, condition_field, condition_val):
        cursor = self.db_conn.cursor()
        sql = '''update %s set %s = '%s' where %s = "%s" ''' % (table_name, update_field, update_val, condition_field, condition_val)
        cursor.execute(sql)
        cursor.close()

    def delete_rec(self, table_name, table_fields, field_values):
        cursor = self.db_conn.cursor()
        sql = '''delete from %s where %s = "%s" ''' % (table_name, table_fields, field_values)
        cursor.execute(sql)
        cursor.close()

    def get_table_fields(self,db_name, table_name):
        cursor = self.db_conn.cursor()
        sql = '''select column_name from information_schema.columns 
                     where table_schema='%s' and table_name='%s' 
                     and column_name not in ('id','status','z_create_time','z_update_time')
                ''' % (db_name, table_name)
        cursor.execute(sql)
        table_fields = []
        for row in cursor:
            table_fields.append(row[0])
        return table_fields

    def get_entity_id(self, entity_name):
        cursor = self.db_conn.cursor()
        # 先查询entity_info中是否已存在该entity_name，若存在直接获取company_id
        get_sql = '''select company_id from entity_info where company_name = "%s" limit 1 ''' % entity_name
        cursor.execute(get_sql)
        if cursor.rowcount > 0:
            entity_id = cursor.fetchone()[0]
        else:   # 若 entity_name 不存在，获取entity_info中最大company_id
            get_max_sql = "select max(company_id) from entity_info"
            cursor.execute(get_max_sql)
            max_entity_id = cursor.fetchone()[0]
            entity_id = max_entity_id + 1
            # 同时将该entity_name,entity_id 写入表entity_info中
            insert_sql= "insert into entity_info(company_id,company_name) values (%s, %s)"
            cursor.execute(insert_sql, (entity_id, entity_name))
            self.db_conn.commit()
        return entity_id

    def get_company_name(self, name):
        company_name = ''
        cursor = self.db_conn.cursor()
        # 查询gp_info中是name对应的company_name
        get_sql = '''select company_name from qimingpian.gp_info where name = "%s" limit 1 ''' % name
        cursor.execute(get_sql)
        if cursor.rowcount > 0:
            company_name = cursor.fetchone()[0]
        return company_name


    def commit(self):
        self.db_conn.commit()

    def close(self):
        self.db_conn.close()

参考：https://blog.csdn.net/wysnxzm/article/details/80914574
https://www.cnblogs.com/dawuge/p/10486777.html
https://blog.csdn.net/w469001293/article/details/103634141