Posted By

dylanninin on 12/04/12


Tagged

mysql import python Discuz XLRD


Versions (?)

batch accounts import into discuz with python


 / Published in: Python
 

URL: http://www.dylanninin.com/blog/

batch accounts import into discuz with python

  1. #!/usr/bin/env python
  2. # -*- coding:gbk -*-
  3. """
  4. Abstract:
  5. batch accounts import into discuz with python
  6. History:
  7. 2012-11-28 [email protected] first release
  8. """
  9.  
  10. import MySQLdb
  11.  
  12. db = MySQLdb.connect('192.168.1.123', 'root', 'toor', 'discuz',
  13. init_command = 'set names gbk')
  14. cursor = db.cursor()
  15. seed = 'example'
  16. file = 'bbs_import.log'
  17. error= '0_error.log'
  18. duplicated = '1_duplicated.log'
  19. dismatch = '2_dismatch.log'
  20. existed = '3_existed.log'
  21. fname = '2012AccountImport.xls'
  22.  
  23.  
  24. def reset():
  25. with open(error, 'w') as f:
  26. f.truncate()
  27. with open(duplicated, 'w') as f:
  28. f.truncate()
  29. with open(dismatch, 'w') as f:
  30. f.truncate()
  31. with open(existed, 'w') as f:
  32. f.truncate()
  33.  
  34.  
  35. def log(level, message):
  36. import time
  37. if level == 0:
  38. with open(error, 'a') as f:
  39. f.writelines(message + '\n')
  40. elif level == 1:
  41. with open(duplicated, 'a') as f:
  42. f.writelines(message + '\n')
  43. elif level == 2:
  44. with open(dismatch, 'a') as f:
  45. f.writelines(message + '\n')
  46. elif level == 3:
  47. with open(existed, 'a') as f:
  48. f.writelines(message + '\n')
  49. with open(file, 'a') as f:
  50. message = time.strftime('%Y-%m-%d %H:%M:%S ') + '=> ' + message
  51. f.writelines(message + '\n')
  52.  
  53.  
  54. def readData():
  55. """read data from excel file which is to be imported"""
  56. from xlrd import open_workbook
  57. """read data from excel"""
  58. wb = open_workbook(fname)
  59. values = []
  60. s = wb.sheet_by_index(0)
  61. for row in range(s.nrows):
  62. value = []
  63. for col in range(s.ncols):
  64. val = s.cell(row, col).value
  65. value.append([val])
  66. values.append(value)
  67. return values
  68.  
  69.  
  70. def existUC(username, email):
  71. """check whether this user has already existed!"""
  72. query="""select username, email
  73. from uc_members m
  74. where m.username = '%s'
  75. or m.email = '%s'""" % (username, email)
  76. cursor.execute(query)
  77. rows = cursor.fetchall()
  78. if len(rows) > 0:
  79. for row in rows:
  80. if not email == row[1]:
  81. "different users have the same username"
  82. log(1, 'username duplicated in UC : %s\t%s\t\t\t%s\t%s' %
  83. (username, email, row[0], row[1]))
  84. return True
  85. elif not username == row[0]:
  86. log(2, 'username dismatched in UC : %s\t%s\t\t\t%s\t%s' %
  87. (username, email, row[0], row[1]))
  88. return True
  89. elif email == row[1]:
  90. log(3, 'user existed in UC : %s\t%s' % (username, email))
  91. return True
  92. else:
  93. return False
  94. else:
  95. return False
  96.  
  97.  
  98. def existCDB(username, email):
  99. """check whether this user has already existed!"""
  100. query="""select username, email
  101. from cdb_members m
  102. where m.username = '%s'
  103. or m.email = '%s'""" % (username, email)
  104. cursor.execute(query)
  105. rows = cursor.fetchall()
  106. if len(rows) > 0:
  107. for row in rows:
  108. if not email == row[1]:
  109. "different users have the same username"
  110. log(1, 'username duplicated in CDB: %s\t%s\t\t\t%s\t%s' %
  111. (username, email, row[0], row[1]))
  112. return True
  113. elif not username == row[0]:
  114. log(2, 'username dismatched in CDB: %s\t%s\t\t\t%s\t%s' %
  115. (username, email, row[0], row[1]))
  116. return True
  117. elif email == row[1]:
  118. log(3, 'user existed in CDB: %s\t%s' % (username, email))
  119. return True
  120. else:
  121. return False
  122. else:
  123. return False
  124.  
  125.  
  126. def seedUC(seed):
  127. """construct a seed from existed rows"""
  128. query="""select * from uc_members m where m.username = '%s'""" % seed
  129. cursor.execute(query)
  130. rows = cursor.fetchall()
  131. if len(rows) == 1:
  132. example = rows[0]
  133. else:
  134. raise StandardError('user %s does not exist!' % seed)
  135. return example
  136.  
  137.  
  138. def seedCDB(seed):
  139. """construct a seed from existed rows"""
  140. query="""select * from cdb_members m where m.username = '%s'""" % seed
  141. cursor.execute(query)
  142. rows = cursor.fetchall()
  143. if len(rows) == 1:
  144. example = rows[0]
  145. else:
  146. raise StandardError('user %s does not exist!' % seed)
  147. return example
  148.  
  149.  
  150. def getNextUid():
  151. """get the next uid to be generated"""
  152. query="""select max(uid) from uc_members"""
  153. cursor.execute(query)
  154. row = cursor.fetchone()
  155. uid = row[0] + 1
  156. return uid
  157.  
  158.  
  159. def constructUC(user):
  160. """construct UC data to be imported"""
  161. username = user[0][0].encode('gbk')
  162. email = user[1][0].encode('gbk')
  163. if not existUC(username, email):
  164. uc = seedUC(seed)
  165. uid = getNextUid()
  166. uc = (uid, ) + uc[1:]
  167. uc = uc[:1] + (username, ) + uc[2:]
  168. uc = uc[:3] + (email, ) + uc[4:]
  169. return uc
  170. else:
  171. return None
  172.  
  173.  
  174. def constructCDB(example):
  175. """construct CDB data to be imported"""
  176. username = example[1]
  177. email = example[3]
  178. uid = example[0]
  179. if not existCDB(username, email):
  180. cdb = seedCDB(seed)
  181. cdb = (uid, ) + cdb[1:]
  182. cdb = cdb[:1] + (username, ) + cdb[2:]
  183. cdb = cdb[:28] + (email, ) + cdb[29:]
  184. return cdb
  185. else:
  186. return None
  187.  
  188.  
  189. def importCDB(example):
  190. """import data into mysql database"""
  191. insert="insert into cdb_members values\
  192. (%d, '%s', '%s', '%s', %d, %d, %d, %d, '%s', '%s',\
  193. %d, '%s', %d, '%s', %d, %d, %d, %d, %d, %d,\
  194. %d, %d, %d, %d, %d, %d, %d, %d, '%s', '%s',\
  195. %d, %d, %d, %d, %d, %d, %d, %d, %d, %d,\
  196. '%s', %d, %d, %d, %d, %d, %d\
  197. )\
  198. " % example
  199. try:
  200. cursor.execute(insert)
  201. except StandardError, e:
  202. log(0, e)
  203. db.rollback()
  204. db.commit()
  205.  
  206.  
  207. def importUC(example):
  208. """import data into mysql database"""
  209. insert="insert into uc_members values\
  210. (%d, '%s', '%s', '%s', '%s', '%s', '%s', %d, %d, %d,\
  211. '%s', '%s'\
  212. )\
  213. " % example
  214. try:
  215. cursor.execute(insert)
  216. except StandardError, e:
  217. log(0, e)
  218. db.rollback()
  219. cdb = constructCDB(example)
  220. if not cdb == None:
  221. importCDB(cdb)
  222. db.commit()
  223.  
  224.  
  225. def check():
  226. reset()
  227. log(-1, '')
  228. log(-1, '*' * 80)
  229. log(-1, 'check data start')
  230. data = readData()
  231. for user in data:
  232. username = user[0][0].encode('gbk')
  233. email = user[1][0].encode('gbk')
  234. existUC(username, email)
  235. existCDB(username, email)
  236. log(-1, 'check data ended')
  237. log(-1, '*' * 80)
  238. log(-1, '')
  239.  
  240.  
  241. def importData():
  242. reset()
  243. log(-1, '')
  244. log(-1, '*' * 80)
  245. log(-1, 'import data start')
  246. data = readData()
  247. for row in data:
  248. uc = constructUC(row)
  249. if not uc == None:
  250. importUC(uc)
  251. log(-1, 'import data ended')
  252. log(-1, '*' * 80)
  253. log(-1, '')
  254.  
  255. if __name__ == '__main__':
  256. check()

Report this snippet  

You need to login to post a comment.