How to Convert List to Table in Python

Here is the logic to convert a list to a table in Python. The code will do two things. Write list data to a DataFrame and save it to a table. From the table, you can query and get insights. We can do it in two steps. First, write list data for the data frame. Second, from it write into a table for future analysis.

Step 01: Write data to DataFrame

In the first statement, you imported the Pandas library. There are two lists – names and grades. Then the zip function compresses these two lists. The df objects contain the DataFrame.

## Python program to convert list to Table
import pandas as pd 
data=(("Srini", 20), ("Vasu", 30))
columns=(["Name", "Age"])
df=pd.DataFrame(data=data, columns=columns)
print("DataFrame data before loading into Table", df) 

Step 02: Export data to a Table

Here you imported the sqlite3 database. The con object connects to the database. Here mydb.db. Next, the df. to_sql exports data to the SQLite table. Below is the self-explanatory syntax. The logic is helpful for data analytics engineers.

# Connect to the SQLite database
import sqlite3 as lite 
conn = lite.connect('mydb.db') 
# Write df to Table
df.to_sql('mytable',
    conn,
    schema=None,
    if_exists='replace',
    index=True,
    index_label=None,
    chunksize=None,
    dtype=None)

# Create a cursor
cursor = conn.cursor()

# Execute the cursor
cursor.execute("SELECT * from mytable")

# Fetch the result
result = cursor.fetchall()

# Read result line by line 
print("Data pulled from the Table...")
for row in result:
    print(row)

# Close the connection
conn.close()

References

• How to use Pandas Sqlite pdf