从postgresql中 导数据到 mysql

从postgres中导出数据到csv中

1  用postgres用户（root）登录postgresql

2 建一个csv文件，并 chmod 777


3 在原数据中修改----title中的;改成:(有;的标题在导出到csv中时会被拆分成两列)
update article_article set title =  'China’s Copycats: Online vs. Offline.' where id=7939;
update article_article set title =  'Web browsers used most in China: how this impacts online campaigns.' where id=7804;
update article_article set title =  'Web browsers used most in China: how this impacts online campaigns.' where id=7926;
update article_article set title =  'Douban.com: China’s Amazon/Digg Hybrid Social Media Network.' where id=7973;
update article_article set title ='“China average daily salary is RMB 111.99???”: Chinese netizens challenge government statistics. ' where id=8029;
update article_article set title =  'China Telecom: Becoming the telephone version of Google.' where id=8072;
update article_article set title =  'The Rich Tighten Pockets: Online Shopping is New Obsession' where id=8169;
update article_article set title =  'The Good, The Bad And The Foreigner: Learning To Accept Myself Through The Eyes Of Others.' where id=7541;
update article_article set title =  'Johnson & Johnson: Big girls don’t cry… or let their bones break.' where id=8066;
select * from article_article where title like '%;%';

4----超级用户postgres，  登录
运行sql
COPY (SELECT * FROM article_article limit 50) TO '/home/david/myfile.csv' WITH CSV HEADER;
5--把导出的csv文件的第一行（culomn名删了）
csv导入mysql
1----mysql 是从 /var/lib/mysql/blog （blog是数据库名）这个目录下读文件的，所以把csv放到此目录下
然后在mysql运行sql命令：
load data infile 'myfile.csv'
into table blog.article
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n';

***问题
postgres中boolean是用true和false表示 （在csv中是t和f）
但mysql中是用1和0（tinyint）表示，直接导入的话在mysql中都是显示0
把t和f改成1和0
2----导入后用py脚本update布尔值

#encoding:utf-8
import psycopg2
import MySQLdb

psycopg2_conn = psycopg2.connect(
        #database="expat", 
        database="postgres", 
        user="david", 
        password="1", 
        host="localhost", 
        port="5432"
    )

mysql_conn= MySQLdb.connect(
        host='localhost',
        port = 3306,
        user='root',
        passwd='1',
        db ='blog',
    )


def import_mysql_boolean():
    '''
    postgres中boolean是用true和false表示 （在csv中是t和f）
    但mysql中是用1和0（tinyint）表示，直接导入的话在mysql中都是显示0
    直接改csv把t和f改成1和0导致导入mysql的某些数据column对不上

    先把原数据导入mysql，再通过这个函数update布尔值
    '''
    cur = psycopg2_conn.cursor()
    cur.execute('''
        select id,is_approved, is_special, is_welcome, is_shf_featured,
            is_shf_sponsored, is_homepage_sponsored, is_home_featured
        from article_article 
        --where id in (8029 ,10691,  10099, 8810, 8936, 8939 , 10126 ,10128 ,10222 ,9217 ,9178,7898,7899)
        --limit 1000
        ''')
    rows = cur.fetchall() 
    #print rows

    cur.close()
    psycopg2_conn.commit()
    psycopg2_conn.close()

    cur2 = mysql_conn.cursor()

    for row in rows:
        arow = list()
        arow.append(row[0])
        arow.append(1) if row[1] is True else arow.append(0) # is_approved
        arow.append(1) if row[2] is True else arow.append(0) # is_special
        arow.append(1) if row[3] is True else arow.append(0) # is_welcome
        arow.append(1) if row[4] is True else arow.append(0) # is_shf_featured
        arow.append(1) if row[5] is True else arow.append(0) # is_shf_sponsored
        arow.append(1) if row[6] is True else arow.append(0) # is_homepage_sponsored
        arow.append(1) if row[7] is True else arow.append(0) # is_home_featured
        sql = '''
        update article set 
            is_approved={is_approved}, 
            is_special={is_special}, 
            is_welcome={is_welcome}, 
            is_shf_featured={is_shf_featured}, 
            is_shf_sponsored={is_shf_sponsored}, 
            is_homepage_sponsored={is_homepage_sponsored}, 
            is_home_featured={is_home_featured}
        where id = {id}
        '''.format(id=arow[0],
                   is_approved=arow[1],
                   is_special=arow[2],
                   is_welcome=arow[3],
                   is_shf_featured=arow[4],
                   is_shf_sponsored=arow[5],
                   is_homepage_sponsored=arow[6],
                   is_home_featured=arow[7],

            )

        print sql
        cur2.execute(sql) 
    cur2.close()
    mysql_conn.commit()
    mysql_conn.close()

 

有一种情况就是表的某个字段大于csv文件的单元格的限制，这时如果编辑保存csv文件后，实际上这个过大的单元格会被拆分成两个单元格，再导入mysql中也就是表中的两个字段，这会导致表字段不对齐，碰到这种问题就是别去编辑csv文件，先把从postgresql导出的csv直接导入mysql，之后在mysql中用sql修改就不会出错

 

 

还有一种需要转义的情况 比如  要在表格中插入 ‘App\Article’ ，在python中写成 'App\\\Article'， 它会被python转义一次，在插入mysql时又会被转一次，最终在mysql的表中就会表示成 ‘App\Article’