0

I am using GridDB Python Client and I have a container that stores my database and I need to get the dataframe object converted to a list of lists. The read_sql_query function offered by pandas returns a dataframe but I need to get the dataframe returned as a list of lists instead of a dataframe. The first element in the list of lists is for the header of the dataframe (the column names) and the other elements are for the rows in the dataframe. Is there a way I could do that? Please help Here is the code for the container and the part where the program reads SQL queries:

#...
import griddb_python as griddb
import pandas as pd
from pprint import pprint

factory = griddb.StoreFactory.get_instance()

# Initialize container
try:
    gridstore = factory.get_store(host="127.0.0.1", port="8080", 
            cluster_name="Cls36", username="root", 
            password="")

    conInfo = griddb.ContainerInfo("Fresher_Students",
                    [["id", griddb.Type.INTEGER],
                     ["First Name",griddb.Type.STRING],
                     ["Last Name", griddb.Type.STRING],
                     ["Gender", griddb.Type.STRING],
                     ["Course", griddb.Type.STRING]
                    ],
                    griddb.ContainerType.COLLECTION, True)
    cont = gridstore.put_container(conInfo)   
    cont.create_index("id", griddb.IndexType.DEFAULT)
    data = pd.read_csv("fresher_students.csv")
    #Add data
    for i in range(len(data)):
        ret = cont.put(data.iloc[i, :])
    print("Data added successfully")

except griddb.GSException as e:
    print(e)

sql_statement = ('SELECT * FROM Fresher_Students')
sql_query = pd.read_sql_query(sql_statement, cont)

def convert_to_lol(query):
    # Code goes here
    # ...
LOL = convert_to_lol(sql_query.head()) # Not Laughing Out Load, it's List of Lists
pprint(LOL)
#...

I want to get something that looks like this:

[["id", "First Name", "Last Name", "Gender", "Course"],
 [0, "Catherine", "Ghua", "F", "EEE"],
 [1, "Jake", "Jonathan", "M", "BMS"],
 [2, "Paul", "Smith", "M", "BFA"],
 [3, "Laura", "Williams", "F", "MBBS"],
 [4, "Felix", "Johnson", "M", "BSW"],
 [5, "Vivian", "Davis", "F", "BFD"]]
Mary Williams
  • 21
  • 2

1 Answers1

0

[UPDATED]

The easiest way I know about(for any DF):

df = pd.DataFrame({'id':[2, 3 ,4], 'age':[24, 42, 13]})
[df.columns.tolist()] + df.reset_index().values.tolist()

output:

[['id', 'age'], [0, 2, 24], [1, 3, 42], [2, 4, 13]]
meti
  • 1,921
  • 1
  • 8
  • 15