python如何解决循环多次读取mysql表数据生成相应的DML sql语句
请教各位python大神：最近本人在处理数据时，想使用python将mysql数据库多张表的变更的记录生成DML SQL语句同步到另外一个postgres数据库。不知道用python怎么实现的。
其中，测试表的结构如下：
表一：
+------+--------+------+-------+-----------+
| id | name | age| depno | dept_name |
+------+--------+------+-------+-----------+
| 1 | Lily | 21 | 1 | name1 |
| 3 | Kate | 23 | 2 | name3 |
| 4 | Bob | 24 | 2 | name4 |
| 5 | Sandy| 25 | 3 | name5 |
| 6 | Sally | 26 | 3 | name6 |
| 7 | John | 27 | 4 | name7 |
| 8 | Betty | 28 | 4 | name8 |
| 9 | Li ming | 29 | 5 | name9 |
| 10 | Li hua| 30 | 5 | name10 |
| 11 | Kevin| 31 | 6 | name11 |
| 2 | Peter| NULL |NULL| NULL |
表二：
+-------+-----------+
| depno | dept_name |
+-------+-----------+
| 1 | name1 |
| 2 | name3 |
| 2 | name4 |
| 3 | name5 |
| 3 | name6 |
| 4 | name7 |
| 4 | name8 |
| 5 | name9 |
| 5 | name10 |
| 6 | name11 |
| 7 | name12 |
+-------+-----------+
变更记录表：
+------+-------------+------------+----------+---------------+---------+---------------------+--------+
| id | schema_name | table_name | opt_type | pk_id | content | log_date| status |
+------+-----------------+---------------+----------+---------------------+---------+---------------------+--------+
| 1001 | use_test | test | update | 9 | | 2017-07-24 13:52:35 | 0 |
| 1005 | use_test | test | insert | 3 | | 2017-07-24 13:52:35 | 0 |
| 1007 | use_test | department | update | 6 | | 2017-07-24 15:30:04 | 0 |
| 1008 | use_test | test | update | 9 | | 2017-07-25 10:29:43 | 0 |
| 1009 | use_test | department | insert | 7 | | 2017-07-25 12:05:11 | 0 |
本人写的脚本运行时，不知道怎么根据不同的表生成相应的insert 和update语句，因为每个表的字段都不一样，麻烦各位帮忙解决写，或者是附件的脚本要怎样修改才能实现，谢谢
本帖最后由 sancaiyujia 于 2017-07-27 09:46 编辑
import MySQLdb as mdb
import sys
def insert_db(param1,param2,param3):
db = mdb.connect(host='localhost',user='root',passwd='Password_123456',db='use_test',port=3306,charset='utf8')
cur = db.cursor()
insertsql="select * from %s.%s where id= %s" %(param1,param2,param3)
global result
try:
cur.execute(insertsql)
result = cur.fetchall()
if param2 == "test":
print 'insert into %s.%s values("%s","%s","%s","%s","%s");' %(param1,param2,result,result,result,result,result)
else :
print result
print 'insert into %s.%s values(%s,%s);' %(param1,param2,result,result)
except:
print("error: sql:" + insertsql+" result:"+str(result))
cur.close()
db.close()
return result
def update_db(param1,param2,param3):
db = mdb.connect(host='localhost',user='root',passwd='Password_123456',db='use_test',port=3306,charset='utf8')
cur = db.cursor()
updatesql="select * from %s.%s where id= %s" %(param1,param2,param3)
print updatesql
global result2
try:
cur.execute(updatesql)
result2 = cur.fetchall()
if param2 == "test":
print 'update %s.%s set id="%s",name="%s",age="%s",depno="%s",dept_name="%s";' %(param1,param2,result2,result2,result2,result2,result2)
else:
print 'update %s.%s set depno="%s",dept_name="%s";' %(param1,param2,result2,result2)
except:
print("error: sql:" + updatesql+" result:"+str(result2))
cur.close()
db.close()
return result2
if __name__ == "__main__":
conn =mdb.connect(host='localhost',user='root',passwd='Password_123456',db='use_test',port=3306,charset='utf8')
with conn:
cur = conn.cursor()
cur.execute("SELECT * FROM l_table_modify_d order by 1")
numrows = int(cur.rowcount)
for i in range(numrows):
row = cur.fetchone()
if row == "insert":
insert_db(row,row,row)
# else:
# update_db(row,row,row)
cur.close()
conn.close()
----麻烦大家看看上面怎么修改可以用于一个脚本适应多张不同表，谢谢 本帖最后由 sancaiyujia 于 2017-07-27 09:50 编辑
请各位大拿帮忙解决下，谢谢！
页:
[1]