python批量插入mysql数据库（性能相关）以及反引号的使用

参考link：

https://blog.csdn.net/qq_35958094/article/details/78462800（插入相关）

https://www.cnblogs.com/hyace/p/4173831.html（效率相关）

最近自己参考上面的link写了一个批量插入数据库的小脚本，记录一下，附上代码：

#!/usr/bin/python
# -*- coding:utf-8 -*-

import pymysql

class InsertData():
    def __init__(self, db_name):
        # connect to database
        self.db = pymysql.connect('127.0.0.1',
                             'root',
                             '123456',
                             db_name,
                             charset='utf8')

        self.cursor = self.db.cursor()

        # init fake data
        self.coll_policy_id = 1   # int
        self.name = 'cisco'
        self.cli_command = 'show run'
        self.cli_command_result = 'good'
        self.desc = '15s'
        self.policy_type = 6   # int
        self.snmp_oid = '1.1.1.1'
        self.history = 'ok'
        self.value_type = 9    # int
        self.ostype_id = 10    # int

    def insert_data(self, table_name):
        # insert into database
        for t in range(0, 20):
            sql = "INSERT INTO " + table_name + " (coll_policy_id, `name`, cli_command, cli_command_result, `desc`, policy_type, snmp_oid, history, value_type, ostype_id) VALUES"
        # % (coll_policy_id, name, cli_command, cli_command_result, desc, policy_type, snmp_oid, history, value_type, ostype_id)
            for i in range(1, 10000):
                self.coll_policy_id = t*10000 + i
                self.value_type = t*10000 + i
                sql += "(" + `self.coll_policy_id` + "," + `self.name`+ "," + `self.cli_command` + "," + `self.cli_command_result` + "," + `self.desc` + "," + `self.policy_type` + "," + `self.snmp_oid` + "," + `self.history` + "," + `self.value_type` + "," + `self.ostype_id` + "),"
            self.coll_policy_id = (t+1)*10000
            self.value_type = (t+1)*10000
            sql += "(" + `self.coll_policy_id` + "," + `self.name`+ "," + `self.cli_command` + "," + `self.cli_command_result` + "," + `self.desc` + "," + `self.policy_type` + "," + `self.snmp_oid` + "," + `self.history` + "," + `self.value_type` + "," + `self.ostype_id` + ")"

            print 'sql command created successfully..'
            print sql

            try:
                self.cursor.execute(sql)
                self.db.commit()
                print 'sql command executed successfully..'
            except Exception,e:
                self.db.rollback()
                print 'database rollback..' + str(e)
        # close database
        self.db.close()

        print 'run successfully'

if __name__ == '__main__':
    inset_data = InsertData('apolo_test')
    inset_data.insert_data('coll_policy')

在执行过程中由于sql语句中出现了一些关键字导致运行报错。这次是在关键字外用反引号(`)解决了这个问题。
`,repr(),str()都是将任意值转换成字符串类型的值。
`与repr()输出相同，方面机器阅读，str()输出则是方便人阅读。
例如：
>>> s = 'hi'
>>> print s
hi
>>> `s`
"'hi'"
>>> repr(s)
"'hi'"
>>> repr(1)
'1'
>>> str(s)
'hi'
>>> print `s`
'hi'

>>> h = '\n'
>>> print h

>>> print `h`
'\n'
>>> `h`
"'\\n'"

巴特西

python批量插入mysql数据库（性能相关）以及反引号的使用

最新文章

热门文章