CODEWITHSUNDEEP
pythonmysqldatabasemysql-workbenchselect-into-outfile
None
None

Reputation: 281

MySQL: wiered chopping for string when using "SELECT INTO OUTFILE"

I exported two fields: name and header from the database using:

SELECT name, header 
INTO OUTFILE '/var/lib/mysql-files/myfile.txt'
FIELDS TERMINATED BY '<xx>' 
LINES TERMINATED BY '\n'
FROM mytable;

One record has this header value:

{'Date': 'Fri, 19 Apr 2019 07:23:14 GMT', 'Server': 'Apache', 'Vary': 'Qualys-Scan', 'Strict-Transport-Security': 'max-age=31536000;includeSubDomains;preload', 'Set-Cookie': 'ASP.NET_SessionId=ivoa5bhet0s2ygkylmimvkie; path=/; secure; HttpOnly;SameSite=strict, SC_ANALYTICS_GLOBAL_COOKIE=12f133ea5080403692b4ce458fd1a540; expires=Thu, 19-Apr-2029 07:23:14 GMT; path=/; secure; HttpOnly;SameSite=strict, SC_ANALYTICS_SESSION_COOKIE=336B597E7A534D6393C57DF11E047484|1|ivoa5bhet0s2ygkylmimvkie; path=/; secure; HttpOnly;SameSite=strict, incap_ses_885_270026=cDp/VlO1AHgshF9F6SZIDGJ3uVwAAAAAg7DwpecyehBCyhXgoYO5GA==; path=/; Domain=.zurich.co.uk, ___utmvmykuNyVY=dlNaoEsuXSO; path=/; Max-Age=900, ___utmvaykuNyVY=nWJx01KvGT; path=/; Max-Age=900, ___utmvbykuNyVY=JZy XEtOwalQ: PtR; path=/; Max-Age=900', 'X-Content-Type-Options': 'nosniff', 'X-XSS-Protection': '1; mode=block', 'Cache-Control': 'private', 'Content-Type': 'text/html; charset=utf-8', 'Keep-Alive': 'timeout=5, max=10', 'Connection': 'Keep-Alive', 'X-Iinfo': '8-3925806-3925807 NNNN CT(73 151 0) RT(1555658593583 5) q(0 0 3 0) r(6 6) U5', 'X-CDN': 'Incapsula', 'Content-Encoding': 'gzip', 'Transfer-Encoding': 'chunked'}

It is exported as:

https://z.co.uk<xx>{'Date': 'Fri, 19 Apr 2019 07:23:14 GMT', 'Server': 'Apache', 'Vary': 'Qualys-Scan', 'Strict-Transport-Security': 'max-age=31536000;includeSubDomains;preload', 'Set-Cookie': 'ASP.NET_SessionId=ivoa5bhet0s2ygkylmimvkie; path=/; secure; HttpOnly;SameSite=strict, SC_ANALYTICS_GLOBAL_COOKIE=12f133ea5080403692b4ce458fd1a540; expires=Thu, 19-Apr-2029 07:23:14 GMT; path=/; secure; HttpOnly;SameSite=strict, SC_ANALYTICS_SESSION_COOKIE=336B597E7A534D6393C57DF11E047484|1|ivoa5bhet0s2ygkylmimvkie; path=/; secure; HttpOnly;SameSite=strict, incap_ses_885_270026=cDp/VlO1AHgshF9F6SZIDGJ3uVwAAAAAg7DwpecyehBCyhXgoYO5GA==; path=/; Domain=.zurich.co.uk, ___utmvmykuNyVY=dlNaoEsuXSO; path=/; Max-Age=900, __utmvaykuNyVY=nWJx01KvGT; path=/; Max-Age=900, ___utmvbykuNyVY=JZy

And in a new line (please note that it begins with a tab that's why stack overflow displays it as a code): 

XEtOwalQ: PtR; path=/; Max-Age=900', 'X-Content-Type-Options': 'nosniff', 'X-XSS-Protection': '1; mode=block', 'Cache-Control':

'private', 'Content-Type': 'text/html; charset=utf-8', 'Keep-Alive': 'timeout=5, max=10', 'Connection': 'Keep-Alive', 'X-Iinfo': '8-3925806-3925807 NNNN CT(73 151 0) RT(1555658593583 5) q(0 0 3 0) r(6 6) U5', 'X-CDN': 'Incapsula', 'Content-Encoding': 'gzip', 'Transfer-Encoding': 'chunked'}

Why this has happened? How to avoid this?

It is causing me a big problem and appear in many other record (but not all).

I need to read the file lines using python, and python identify the chopped line as two lines instead of one, which makes the lines do not conform to the line format that I process with python and I get error saying out of index.

Upvotes: 0

Views: 59

Answers (1)

Michael
Michael

Reputation: 5335

Something like this (not tested):

with open('/var/lib/mysql-files/myfile.txt') as f:
    lines = f.read().splitlines()
i = 0
lines2 = []
while i<len(lines):
    if ('{' in lines[i]) and ('}' not in lines[i]):
        l = lines[i] + ' ' + lines[i+1] + '\n'
        i += 1
    else:
        l = lines[i] + '\n'
    lines2.append(l)
    i += 1

with open('/var/lib/mysql-files/fixed.txt', 'w') as f:
    f.writelines(lines2)

Upvotes: 0

Related Questions