用python把postgresql中的表内容复制到mysql中

postgresql中的表和mysql中的表结构不一样，过程中做点转换，特别是datetime的insert

还有activated 字段在原表中是布尔型，mysql表中是tinyint型

 

 

import psycopg2
import MySQLdb
import datetime, time


psycopg2_conn = psycopg2.connect(
        database="expat", 
        user="david", 
        password="1", 
        host="localhost", 
        port="5432"
    ) 
mysql_conn= MySQLdb.connect(
        host='localhost',
        port = 3306,
        user='root',
        passwd='1',
        db ='blog',
    )


def get_user_data():
    cur = psycopg2_conn.cursor()
    cur.execute("SELECT * FROM auth_user;")
    rows = cur.fetchall() 
    cur.close()
    psycopg2_conn.commit()
    psycopg2_conn.close()

    return rows
#get_user_data()


def save_user_to_mysql(rows):
    cur = mysql_conn.cursor()
    for row in rows:
        newrow = list()
        newrow.append(row[0]) # 0 id 
        newrow.append(row[4]) # 1 name
        newrow.append(row[7]) # 2 email
        newrow.append('') # 3 password
        newrow.append(1) if row[9] is True else newrow.append(0) # 5 activated
        newrow.append(0)      # 6 banned
        r2 = row[2]
        last_login = datetime.datetime(r2.year, r2.month, r2.day, r2.hour, r2.minute, r2.second)
        last_login = last_login.strftime('%Y-%m-%d %H:%M:%S')

        newrow.append(last_login) # 9 last_login
        newrow.append(0) # 12 protected
        r10 = row[10]
        created_at = datetime.datetime(r10.year, r10.month, r10.day, r10.hour, r10.minute, r10.second)
        created_at = created_at.strftime('%Y-%m-%d %H:%M:%S')
        newrow.append(created_at) #  13 created_at
        newrow.append(row[1]) # 16 password_old

        sql = '''
            insert into users(id, name, email, password, activated, banned,last_login,protected, created_at,password_old) 
                       values(%d, '%s', '%s',  '%s',     %s,        %d,    '%s',        %d,        '%s',        '%s')
        ''' % tuple(newrow)
        print sql
        cur.execute(sql) 

        if row[3] is True: # is_superuser is True
            cur.execute('''
                insert into users_groups (user_id, group_id)
                values(%d, 1)
                &