今天在Linux下操作MySQL,也是各种囧啊,首先记录下Linux命令操作MySQL:
[work@*********.com bin]$ ./mysql -u*** -p*** -P3310 -h*******.com
mysql> show databases;
mysql> use jingpin_api_dw;
mysql> show tables;
mysql> show create table dw_deal_shops;
mysql>select * from dw_deal_shops where site_id =7 limit 1;
这是今天在Linux下操作的几个SQL语句,都比较好理解,但当从服务器读取MySQL中的数据时,不能够将读取结果保存到本地,只好用python来操作MySQL,在Python中操作MySQL时需要引入MySQLdb包,连接数据库操作:
conn = MySQLdb.connect(host = HOST, port = PORT, user = USER, passwd = PASSWD,db = DB, charset = CHARSET)
指定游标:
cursor = conn.cursor()
SQL语句:
meituan_sql = 'select cityid,count(distinct bid) from dw_deal_shops where site_id = 6 group by cityid;'
执行SQL语句:
cursor.execute(meituan_sql)
读取SQL语句的内容:
rows = cursor.fetchall()
for row in rows:
city_id =str(row[0])
bid_num = str(row[1])
city_set.add(city_id)
meituan_dict[city_id] = bid_num
在将Python读取的SQL内容写入文本时,当文本为中文字符时遇到编码问题,此时需要进行编码转换:
if city_dict.has_key(e):
output_file.write(city_dict[e].encode('gb2312'))
其中city_dict为Python中的字典,其中存储的是城市id和城市name,城市name为中文,在将其写入文本output_file之前需要将其编码成gb2312格式。