mysql inser decimal_Python把MySQL查询转为Insert语句

#!/usr/bin/env python

# encoding: utf-8

#

import MySQLdb

import sys

import datetime

# 中文输出;

reload(sys)

sys.setdefaultencoding('utf8')

# 连接数据库;

try:

connS = MySQLdb.connect(

host='10.10.0.1',

port=3306,

user='root',

passwd='123456',

db='information_schema',

charset='utf8',

compress=1,

connect_timeout=1

)

except BaseException:

print "Could not connect to MySQL server."

exit(1)

# 查询结果为Json;

cursorS = connS.cursor(cursorclass=MySQLdb.cursors.DictCursor)

# 定义执行函数;

def CreateTableInfo(TableName):

sqlS = "select \

TABLE_SCHEMA, \

TABLE_NAME, \

TABLE_TYPE, \

ENGINE, \

ROW_FORMAT, \

TABLE_ROWS, \

DATA_LENGTH, \

INDEX_LENGTH, \

AUTO_INCREMENT, \

CREATE_TIME, \

UPDATE_TIME, \

TABLE_COLLATION, \

TABLE_COMMENT \

from information_schema.TABLES where TABLE_SCHEMA!='information_schema' and TABLE_SCHEMA!='mysql' and TABLE_SCHEMA!='performance_schema' and TABLE_SCHEMA!='test' and TABLE_SCHEMA!='sys';"

# 判断查询语句是否正常;

try:

cursorS.execute(sqlS)

rsS = cursorS.fetchall()

except Exception as e:

print(e)

exit(1)

# 判断查询结果是否有值;

if not rsS:

print('No Values')

exit(1)

# 处理查询结果;

try:

connS.begin()

for RowDict in rsS:

RowKey = ','.join(str(v) for v in RowDict.keys())

RowValue = ','.join(

'\'' +

str(v) +

'\'' if isinstance(

v,

str) or isinstance(

v,

unicode) or isinstance(

v,

datetime.datetime) else str(v) for v in RowDict.values())

RowValue = RowValue.replace('None', 'NULL')

InsertSQL = "insert into `%s`(%s) values (%s);" % (TableName , RowKey, RowValue)

print(InsertSQL)

connS.commit()

except Exception as e:

print(e)

connS.rollback()

if __name__ == '__main__':

CreateTableInfo('tablename')

cursorS.close()

connS.close()