0

I'm rather new to MySQL so apologies if this is an intuitive problem, I couldn't find anything too helpful in stackoverflow. I have a rather large amount of financial data in one row currently, with each value separated by a comma. 12 values equals one set of data and so I want to create a new row after every 12 values.

In other words, the data I have looks like this: (open_time,open,high,low,close,volume,close_time,quotevol,trades,ignore1,ignore2,ignore3, ...repeat...)

And I'd like for it to look like:

Row1:(open_time,open,high,low,close,volume,close_time,quotevol,trades,ignore1,ignore2,ignore3) Row2:(open_time2,open2,high2,low2,close2,volume2,close_time2,quotevol2,trades2,ignore4,ignore5,ignore6)

Row3: ...

The data is already a .sql file and I have it in a table too if that makes a difference. To clarify, the table it is in has only one row and one column.

Rick James
  • 135,179
  • 13
  • 127
  • 222
Mikey Mike
  • 79
  • 1
  • 9

2 Answers2

0

I don't doubt there is a way to do it in MySQL, but I would approach it by exporting out the record as .CSV.

Export to CSV

Write a simple python script using the CSV module and shift every x number of fields to a new row using the comma as a delimiter. Afterward, you can reimport it back into MySQL.

LegendOfTheFox
  • 1
0

If I understand correctly, you want to do the following:

  1. Get the string from the database, which is located in the first row of the first column in the query results
  2. Break the string into "rows" with 12 values long
  3. Be able to use this data

The way I would go about this in Python is to:

  1. Create a mysql connection and cursor
  2. Execute the query to pull the data from the database
  3. Put the data from the single cell into a string
  4. Split the string at each comma and add those values to a list
  5. Break that list into chunks of 12 elements each
  6. Put this data into a tabular form for easy consumption

Code:

import mysql
import pandas as pd 
query = '''this is your sql statement that returns everything into the first row of the first column in your query results'''
cnx = mysql.connector.connect('''enter relevant connection information here: user, password, host, and database''')
mycursor = cnx.cursor()
mycursor.execute(query)
tup = tuple(mycursor.fetchall()[0])
text = str(tup[0])
ls = text.split(',') # converts text into list of items
n = 12
rows = [ls[i:i + n] for i in range(0, len(ls), n)]
data = []
for row in rows:
    data.append(tuple(row))
labels = ['open_time','open','high','low','close','volume','close_time','quotevol','trades','ignore1','ignore2','ignore3']
df = pd.DataFrame.from_records(data, columns=labels)
print(df)

The list comprehension code was taken from this. You did not specify exactly how you wanted your resultant dataset, but the pandas data frame should have each of your rows.

Without an actual string or dataset, I can't confirm that this works entirely. Would you be able to give us a Minimal, Complete, and Verifiable example?

BenG
  • 304
  • 1
  • 11