python连接mariadb数据库写入数据

1、逻辑

        根据每日生成的一个全网设备信息：设备名、厂商、设备型号、IP、版本、存活时间自动先生成一个.CSV文件，python通过pymysql库连接数据库将设备基础信息csv文件写入数据库。有两种方法进行数据库写入：逐条写入（适合小量）、一次性导入（适合大量的情况）。

1.1 逐条写入

import pymysql

def main():
	try:
		csv_file_path='/home/zhangli/sw_list.csv'
		file = open(csv_file_path, 'r',encoding='utf-8')
		reader = file.readline()
		b = reader.split(',')
		colum = ''
		for a in b:
    		colum = colum + a + ' varchar(255),'
		colum = colum[:-1]
		#定义table名字
		table_name = 'ETCD'
		#定义要插入的数据
		#ata = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES" % (csv_filename,table_name)
		#连接数据库
		conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='admin123',db='NET',charset='utf8')
		#创建游标对象
		cursor = conn.cursor()
		#定义数据库
		database='NET'
		#创建table
		create_table = 'create table if not exists ' + table_name + ' ' + '(' + colum + ')' + ' DEFAULT CHARSET=utf8'
		#执行
		cursor.execute('drop table %s' % table_name)
		cursor.execute('use %s' % database)
		cursor.execute(create_table)
		#创建sql语句
		for line in file:
			line=line.replace("\n","")
			f_read=line.split(',')
			if f_read[0] == 'sw_name':
        		continue
			else:
				sw_name=f_read[0]
				ip=f_read[1]
				data_info=' values('+"\""+sw_name+"\""','+"\""+ip+"\""')'
				sql = 'insert into '+table_name+data_info
				cursor.execute(sql)
		#提交事务
		conn.commit()
		cursor.close()
		conn.close()
	except Exception as err:
        print(err)

main()

1.2 通过LOAD DATA全量导入数据

import pymysql

def main():
        try:
                csv_file_path='/home/zhangli/sw_list.csv'
                file = open(csv_file_path, 'r',encoding='utf-8')
                reader = file.readline()
                b = reader.split(',')
                colum = ''
                for a in b:
                        colum = colum + a + ' varchar(255),'
                colum = colum[:-1]
                #定义table名字
                table_name = 'ETCD'
                #定义要插入的数据
                #data = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES" % (csv_filename,table_name)
                #连接数据库
                conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='admin123',db='NET',charset='utf8',local_infile=True)
                #创建游标对象
                cursor = conn.cursor()
                #定义数据库
                database='NET'
                #创建table
                create_table = 'create table if not exists ' + table_name + ' ' + '(' + colum + ')' + ' DEFAULT CHARSET=utf8'
                #执行
                cursor.execute('use %s' % database)
                #cursor.execute('drop table %s' % table_name)
                cursor.execute(create_table)
                #定义数据写入
                sql="LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;" % (csv_file_path,table_name)
                cursor.execute(sql)
                #print('value1-->',value1)
                #print('value2-->',value2)
                #提交事务
                conn.commit()
                cursor.close()
                conn.close()
        except Exception as err:
                print(err)
main()

1.3 考虑日常数据库更新

在1.2基础上进行优化，考虑日常数据更新

import pymysql

def main():
        try:
                csv_file_path='/home/zhangli/sw_list.csv'
                file = open(csv_file_path, 'r',encoding='utf-8')
                reader = file.readline()
                b = reader.split(',')
                colum = ''
                for a in b:
                        colum = colum + a + ' varchar(255),'
                colum = colum[:-1]
                #定义table名字
                table_name = 'ETCD'
                #定义要插入的数据
                #data = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES" % (csv_filename,table_name)
                #连接数据库
                conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='admin123',db='NET',charset='utf8',local_infile=True)
                #创建游标对象
                cursor = conn.cursor()
                #定义数据库
                database='NET'
                #创建table
                drop_table = 'drop table if exists ' + table_name
                create_table = 'create table if not exists ' + table_name + ' ' + '(' + colum + ')' + ' DEFAULT CHARSET=utf8'
                #执行
                cursor.execute('use %s' % database)
                cursor.execute(drop_table)
                cursor.execute(create_table)
                #定义数据写入
                sql="LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;" % (csv_file_path,table_name)
                cursor.execute(sql)
                #print('value1-->',value1)
                #print('value2-->',value2)
                #提交事务
                conn.commit()
                cursor.close()
                conn.close()
        except Exception as err:
                print(err)
main()