使用Python分割（SQL）大文件

最近重装服务器系统，备份了某网站的数据库文件，备份在一个sql文件中了，大小为100M+，在导入新数据库时因为文件过大，使用phpmyadmin无法导入。

使用notepad打开是时相当的慢，又卡死，无法手动分割文件内容，所以想到了Python来解决。

下面代码写得还不够严谨，但是基本可以解决分割sql大文件的功能。

功能：

1、输入行号，打印此行内容

2、输入起始行号（不含）和结束行号（含），打印区间的内容

3、输入要分割的文件名和要把几个表信息保存在一个文件中（默认把10个表保存在一个文件中）

注：需要修改main函数segm参数默认值

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
@author:         mrdede
@contact:        1234567@qq.com
@blog:           http://mrdede.com
@file:           rean_file.py
@time:           2017/9/6 14:07
@software:       PyCharm 
@Python version: 2.7.13
"""

import re, os

# 分割SQL文件
# @segm  匹配分隔字符串
def main(sql_file, num=10, postfix='', segm='UNLOCK TABLES;'):
    #先得到要分割的文件的起始和结束位置所在行数并放入列表x中
    count,total,j,l = 0, 0, 0, 0
    x = [0,]
    fin = open(sql_file,'r')
    for line in fin:
        total += 1
        if re.search(segm,line):   #以此判断表的数量
            count += 1
            if count % num == 0:   #每到 num 张表时将分割的行数写入x列表中
                #print(count,total)
                x.append(total)

    x.append(len(open(sql_file).readlines())+1)  #将最后一行也放入x中
    # print x

    for i in x:
        # print(i,end = ' ')
        print i,' '

    print(sql_file)

    cnt=1
    tup = os.path.splitext(sql_file)
    # print tup
    if not postfix:
        postfix = tup[1]
    #读取x中的行数，起始和结束位置将文件内容复制到新文件中
    for i,j in enumerate(open(sql_file,'r')):
        if i<=x[cnt]:
            with open((tup[0]+'-%d'+postfix)%cnt,'a+') as f:                          
                f.write(j)
        else:
            cnt+=1
            print("wirte file"+str(cnt))
    print "ok"

# 打印指定行号的内容
# 因为文件太大，电脑内存小，打不开SQL文件
# 所以可以用笨方法来查看某行的内容
def main1(sql_file, lines):
    if not isinstance(lines, int):
        print 'Data type error'
        return False
    count,total = 0, 0
    x = [0,]
    fin = open(sql_file,'r')
    if lines < 0:
        print 'none line', lines
    else:
        tof = True
        for line in fin:
            if lines-1 == total:
                print(line)
                tof = False
                break
            total += 1
        if tof and lines > total:
            print 'Not so much. (line', lines, ')'

# 打印指定行号的内容
# 因为文件太大，电脑内存小，打不开SQL文件
# 所以可以用笨方法来查看某行的内容
# 截取行时不含起始行内容，含终止行内容
# @line1    可以为 0
def main2(sql_file, line1, line2):
    count,total = 0, 0
    x = [0,]
    fin = open(sql_file,'r')
    if line1 < 0:
        print 'none Starting line', line1
    elif line2 < 0:
        print 'none End line', line2
    else:
        tof = True
        for line in fin:
            total += 1
            if line1 >= 0 and line2 >= 0 and line1 < line2 and line1 < total and line2 >= total:
                print line,
                tof = False
                if line2 == total:
                    break
        if tof:
            if line1 > total:
                lines = line1
            elif line2 > total:
                lines = line2
            print 'Not so much. (line', lines, ')'
if __name__=='__main__':
    sql_path = 'backups.sql'
    while True:
        p = input('%s%s%s%s%s' % ('You want to:\n',
                '1.query the content of a single line?\n',
                '2.query Multi-line content?\n',
                '3.Split SQL files?\n',
                'Enter the Numbers 1 or 2 or 3:'))
        iswhile = True
        if p == 1:
            sql_file = sql_path
            while iswhile:
                try:
                    line = input('Please enter line number:')
                except SyntaxError,e:
                    print 'Exit'
                    iswhile = False
                # 输入 0 退出查询
                if line == 0:
                    print 'Exit'
                    iswhile = False
                else:
                    main1(sql_file, line)
        elif p == 2:
            try:
                sql_file = input('SQL file name:')
            except SyntaxError,e:
                # 什么也不输入时则使用变量sql_path的值
                sql_file = sql_path
            print(sql_file)
            while iswhile:
                try:
                    line1 = input('Starting line number:')
                except SyntaxError,e:
                    line1 = 0
                try:
                    line2 = input('End line number:')
                except SyntaxError,e:
                    if not isinstance(line2, int):
                        print 'param "line2" Data type error.'
                    # 什么也不输入则默认读取起始行以下的20行内容
                    line2 = int(line1) + 10
                # 两个变量值都为 0 则退出查询
                if line1 == 0 and line2 == 0:
                    print 'Exit'
                    iswhile = False
                elif line1 == line2:
                    main1(sql_file, line1)
                else:
                    main2(sql_file, line1, line2)
        elif p == 3:
            try:
                sql_file = input('SQL file name:')
            except SyntaxError,e:
                # 什么也不输入时则使用变量sql_path的值
                sql_file = sql_path
            print(sql_file)
            try:
                num = input('A file saves several tables:')
            except SyntaxError,e:
                # 什么也不输入时则num值为 10
                num = 10
            main(sql_file, num)
        else:
            break

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

#!/usr/bin/env python

# -*- coding: utf-8 -*-

"""

@author: mrdede

@contact: 1234567@qq.com

@blog: http://mrdede.com

@file: rean_file.py

@time: 2017/9/6 14:07

@software: PyCharm

@Python version: 2.7.13

"""

import re, os

# 分割SQL文件

# @segm 匹配分隔字符串

def main(sql_file, num=10, postfix='', segm='UNLOCK TABLES;'):

#先得到要分割的文件的起始和结束位置所在行数并放入列表x中

count,total,j,l = 0, 0, 0, 0

x = [0,]

fin = open(sql_file,'r')

for line in fin:

total += 1

if re.search(segm,line): #以此判断表的数量

count += 1

if count % num == 0: #每到 num 张表时将分割的行数写入x列表中

#print(count,total)

x.append(total)

x.append(len(open(sql_file).readlines())+1) #将最后一行也放入x中

# print x

for i in x:

# print(i,end = ' ')

print i,' '

print(sql_file)

cnt=1

tup = os.path.splitext(sql_file)

# print tup

if not postfix:

postfix = tup[1]

#读取x中的行数，起始和结束位置将文件内容复制到新文件中

for i,j in enumerate(open(sql_file,'r')):

if i<=x[cnt]:

with open((tup[0]+'-%d'+postfix)%cnt,'a+') as f:

f.write(j)

else:

cnt+=1

print("wirte file"+str(cnt))

print "ok"

# 打印指定行号的内容

# 因为文件太大，电脑内存小，打不开SQL文件

# 所以可以用笨方法来查看某行的内容

def main1(sql_file, lines):

if not isinstance(lines, int):

print 'Data type error'

return False

count,total = 0, 0

x = [0,]

fin = open(sql_file,'r')

if lines < 0:

print 'none line', lines

else:

tof = True

for line in fin:

if lines-1 == total:

print(line)

tof = False

break

total += 1

if tof and lines > total:

print 'Not so much. (line', lines, ')'

# 打印指定行号的内容

# 因为文件太大，电脑内存小，打不开SQL文件

# 所以可以用笨方法来查看某行的内容

# 截取行时不含起始行内容，含终止行内容

# @line1 可以为 0

def main2(sql_file, line1, line2):

count,total = 0, 0

x = [0,]

fin = open(sql_file,'r')

if line1 < 0:

print 'none Starting line', line1

elif line2 < 0:

print 'none End line', line2

else:

tof = True

for line in fin:

total += 1

if line1 >= 0 and line2 >= 0 and line1 < line2 and line1 < total and line2 >= total:

print line,

tof = False

if line2 == total:

break

if tof:

if line1 > total:

lines = line1

elif line2 > total:

lines = line2

print 'Not so much. (line', lines, ')'

if __name__=='__main__':

sql_path = 'backups.sql'

while True:

p = input('%s%s%s%s%s' % ('You want to:\n',

'1.query the content of a single line?\n',

'2.query Multi-line content?\n',

'3.Split SQL files?\n',

'Enter the Numbers 1 or 2 or 3:'))

iswhile = True

if p == 1:

sql_file = sql_path

while iswhile:

try:

line = input('Please enter line number:')

except SyntaxError,e:

print 'Exit'

iswhile = False

# 输入 0 退出查询

if line == 0:

print 'Exit'

iswhile = False

else:

main1(sql_file, line)

elif p == 2:

try:

sql_file = input('SQL file name:')

except SyntaxError,e:

# 什么也不输入时则使用变量sql_path的值

sql_file = sql_path

print(sql_file)

while iswhile:

try:

line1 = input('Starting line number:')

except SyntaxError,e:

line1 = 0

try:

line2 = input('End line number:')

except SyntaxError,e:

if not isinstance(line2, int):

print 'param "line2" Data type error.'

# 什么也不输入则默认读取起始行以下的20行内容

line2 = int(line1) + 10

# 两个变量值都为 0 则退出查询

if line1 == 0 and line2 == 0:

print 'Exit'

iswhile = False

elif line1 == line2:

main1(sql_file, line1)

else:

main2(sql_file, line1, line2)

elif p == 3:

try:

sql_file = input('SQL file name:')

except SyntaxError,e:

# 什么也不输入时则使用变量sql_path的值

sql_file = sql_path

print(sql_file)

try:

num = input('A file saves several tables:')

except SyntaxError,e:

# 什么也不输入时则num值为 10

num = 10

main(sql_file, num)

else:

break

织梦先生

一个从零开始并且为了自己心底的理想不断奋斗中的人

使用Python分割（SQL）大文件