# -*- coding: UTF-8 -*-
"""
@version: python2.7.8
@author: XiangguoSun
@contact: sunxiangguodut@qq.com
@file: connectdb.py
@time: 2016/12/28 16:12
"""
import MySQLdb
import time
def connectdb():
while True:
try:
db = MySQLdb.connect("localhost", "root", "root", "douban")
return db
except MySQLdb.Error,e:
print e.args,"(connectdb) we will try again after 10 seconds..."
time.sleep(10)
下面是对数据库进行操作
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@version: python2.7.8
@author: XiangguoSun
@contact: sunxiangguodut@qq.com
@file: dbfunction.py
@time: 2017/2/10 11:08
@software: PyCharm
"""
import sys
from connectdb import connectdb
reload(sys)
sys.setdefaultencoding('utf-8')
def fun(tablename):
db = connectdb()
cursor = db.cursor()
print "start transform",tablename,"..."
sql = "SELECT COUNT(*) as repetitions, uid, time " \
"FROM " + tablename + " " \
"GROUP BY uid, time " \
"HAVING repetitions > 1"
cursor.execute(sql)
an = cursor.fetchall()
print "duplicate data: ",len(an)
sql = "SELECT uid, `time` " \
"FROM " + tablename + " " \
"GROUP BY uid, time"
cursor.execute(sql)
print "create tmp..."
sql = "CREATE TABLE tmp SELECT `uid`, `likers_count`, `time`, `url`, `comments_count`, `activity`, `type`, `content`, `crawtime`" \
"FROM " + tablename + " GROUP BY uid, time"
cursor.execute(sql)
print "create tmp done!"
print "drop table "+tablename+"..."
sql = "DROP TABLE " + tablename
cursor.execute(sql)
print "drop table "+tablename+"done!"
print "rename table tmp as"+tablename+"..."
sql = "ALTER TABLE tmp RENAME TO " + tablename
cursor.execute(sql)
print "rename done!"
print "add key..."
sql = "ALTER TABLE " + tablename + " ADD PRIMARY KEY (`uid`,`time`)"
cursor.execute(sql)
print "key added done!"
print "succeed!"
db = connectdb()
for i in range(1,200):
fun("userlifestream_"+str(i))
print "all done!"
请注意转义符号的使用··,尤其是在sql语句GROUP 等
还有增加联合主键:没有转意字符会报错
转义操作符是键盘上~键对应的
print "add key..." sql = "ALTER TABLE " + tablename + " ADD PRIMARY KEY (`uid`,`time`)" cursor.execute(sql) print "key added done!"