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)
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()`?