从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()
还有一种需要转义的情况 比如 要在表格中插入 ‘App\Article’ ,在python中写成 'App\\\Article', 它会被python转义一次,在插入mysql时又会被转一次,最终在mysql的表中就会表示成 ‘App\Article’