read and write to SQLite database table from Pandas DataFrame to

to_sql(): Data from Pandas DataFrame to SQLite database table #B05

DataFrame to SQlite table in Colab platform using to_sql()

Inside Colab platform (left side ) use the session drive and upload the sample student excel file.
Colab file upload during Runtime

Create the DataFrame using read_excel() and the student Excel file.

import pandas as pd
df=pd.read_excel('student.xlsx') # DataFrame created using student file

read_excel() to create DataFrame from Excel at Colab

Create the SQlite database and store the same in session drive.

from sqlalchemy import create_engine,text
from sqlalchemy.exc import SQLAlchemyError
path="sqlite:///my_data.db" # create a SQLite database in google colab session storage
my_conn = create_engine(path)
my_conn=my_conn.connect() # connection is established 
try:    
    result = my_conn.execute(text("select name from sqlite_master where type = 'table'"))
    for row in result:
      print(row) # Display all tables in SQLite database 
except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        print(error)

Creating SQlite database in Colab Platform

Check the table list from SQLite database

df.to_sql(con=my_conn,name='student') # create student table in sqlite database

try:    
    result = my_conn.execute(text("select name from sqlite_master where type = 'table'"))
    for row in result:
      print(row) # List all tables in our SQLite database 
except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        print(error)

('student',)

From student table, display records.

result = my_conn.execute(text("SELECT *  FROM student"))
for row in result:
  print(row) # display row from student table.

Use conditions to get matching records from SQLite database student table.

result = my_conn.execute(text("SELECT *  FROM student WHERE mark > 80 AND class='Five'"))
for row in result:
  print(row) # display row from student table.

Questions

How do I use the `to_sql()` function in Pandas to save a DataFrame to an SQLite database?
What are the required parameters for the `to_sql()` function when working with SQLite?
Can I specify a table name while using the `to_sql()` function with SQLite?
What is the default behavior of the `to_sql()` function when creating a table in SQLite?
How can I specify the data types for the columns when saving a DataFrame to SQLite using `to_sql()`?
Is it possible to append data to an existing table in SQLite using the `to_sql()` function?
How do I handle primary keys or unique constraints when saving a DataFrame to SQLite using `to_sql()`?
What happens if there are duplicate column names in the DataFrame when using `to_sql()` with SQLite?
Can I control the chunk size or batch size for inserting data when using `to_sql()` with SQLite?
How can I handle errors or exceptions when saving a DataFrame to SQLite using `to_sql()`?

Data input and output from Pandas DataFrame Download sample Excel or CSV file or create DataFrame
Pandas DataFrame read_sql read_sql_table insert data

Pandas DataFrame Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

DataFrame to SQLite database at Colab platform using to_sql()

Questions

Subscribe