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

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

DataFrame to SQLite database at Colab platform using to_sql()

Questions

Subhendu Mohapatra