query_romsize.py 3.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147
  1. import sqlite3
  2. import os
  3. import re
  4. import string
  5. import stat
  6. import popen2
  7. import glob
  8. import sys
  9. import os
  10. import pprint
  11. import base64
  12. import getpass
  13. import os
  14. import socket
  15. import sys
  16. import traceback
  17. import paramiko
  18. import socket
  19. from subprocess import Popen
  20. def distance(a,b):
  21. c = {}
  22. n = len(a); m = len(b)
  23. for i in range(0,n+1):
  24. c[i,0] = i
  25. for j in range(0,m+1):
  26. c[0,j] = j
  27. for i in range(1,n+1):
  28. for j in range(1,m+1):
  29. x = c[i-1,j]+1
  30. y = c[i,j-1]+1
  31. if a[i-1] == b[j-1]:
  32. z = c[i-1,j-1]
  33. else:
  34. z = c[i-1,j-1]+1
  35. c[i,j] = min(x,y,z)
  36. return c[n,m]
  37. paramiko.util.log_to_file('rom_sftp.log')
  38. if socket.gethostname() == 'slap':
  39. path = "/home/david/Devel/arch/avr/code/snesram/roms/"
  40. username = "david"
  41. hostname = "slap"
  42. elif socket.gethostname() == 'box':
  43. path = "/Users/david/Devel/arch/avr/code/snesram/roms/"
  44. username = "david"
  45. hostname = "burst"
  46. else:
  47. sys.exit()
  48. def shellquote(s):
  49. return "'" + s.replace("'", "'\\''") + "'"
  50. def main():
  51. port = 22
  52. password = getpass.getpass('Password for %s@%s: ' % (username, hostname))
  53. hostkeytype = None
  54. hostkey = None
  55. host_keys = paramiko.util.load_host_keys(os.path.expanduser('~/.ssh/known_hosts'))
  56. if host_keys.has_key(hostname):
  57. hostkeytype = host_keys[hostname].keys()[0]
  58. hostkey = host_keys[hostname][hostkeytype]
  59. print 'Using host key of type %s' % hostkeytype
  60. try:
  61. print "Connect %s:%s" % (hostname, port)
  62. t = paramiko.Transport((hostname, port))
  63. t.connect(username=username, password=password, hostkey=hostkey)
  64. sftp = paramiko.SFTPClient.from_transport(t)
  65. # dirlist on remote host
  66. dirlist = sftp.listdir('.')
  67. print "Dirlist:", dirlist
  68. conn = sqlite3.connect('roms_cleanup.sqlite3')
  69. c = conn.cursor()
  70. for i in [(1,),(2,),(4,),(8,),(16,)]:
  71. dirname = os.path.join(path,"%02i" % i)
  72. if not os.path.isdir(dirname):
  73. os.mkdir(dirname)
  74. print "#" * 60
  75. print "%i MBit" % i
  76. print "#" * 60
  77. c.execute('''SELECT
  78. rom_name,
  79. rom_mb,
  80. file_name
  81. FROM
  82. roms
  83. WHERE
  84. rom_mb = ?
  85. AND
  86. rom_hirom = 0
  87. AND
  88. rom_sram = 0
  89. AND
  90. rom_type = 0
  91. AND
  92. rom_region like "Europe%"
  93. ORDER BY file_name
  94. ''',i)
  95. last_name = str()
  96. for row in c:
  97. name,size,filename = row
  98. d = distance(os.path.basename(filename),os.path.basename(last_name))
  99. if d < 7:
  100. print "Skip ",filename
  101. continue
  102. filename_dst = os.path.join(dirname,os.path.basename(filename))
  103. print "Remote: %s -> %s" % ( filename,filename_dst)
  104. last_name = filename
  105. try:
  106. sftp.get(filename,filename_dst)
  107. except Exception, e:
  108. print '*** Caught exception: %s: %s' % (e.__class__, e)
  109. traceback.print_exc()
  110. print "Done"
  111. except Exception, e:
  112. print '*** Caught exception: %s: %s' % (e.__class__, e)
  113. traceback.print_exc()
  114. try:
  115. t.close()
  116. except:
  117. pass
  118. sys.exit(1)
  119. t.close()
  120. if __name__ == '__main__':
  121. main()