example of sqlite <> pandas interop

read_write_sqlite_pandas.py

import pandas as pd
import sqlite3
# %%
Cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'Price': [22000,25000,27000,35000]
        }

df = pd.DataFrame(Cars, columns= ['Brand', 'Price'])
print (df)

# %% create database
conn = sqlite3.connect('TestDB1.db'); c = conn.cursor()

# %% create table
c.execute('CREATE TABLE CARS (Brand text, Price number)')
conn.commit()

# %% write to db
df.to_sql('CARS', conn, if_exists='replace', index = False)

# %% read from sqlite
dbpath = "TestDB1.db"
cnx = sqlite3.connect(dbpath)
cursor = cnx.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())
# %%
df = pd.read_sql("SELECT * FROM CARS", cnx)
df.info()

# %%