How To Import Db File In Python

In this tutorial, we will learn how to import a DB (SQLite) file in Python. We will use SQLite as the example database because it is easy to set up and manage using the built-in SQLite module in Python.

SQLite is a widely used, serverless, self-contained, high-reliability, and full-featured SQL database engine. This tutorial will walk you through the process of importing an existing SQLite database file into a Python script so you can interact with the data.

Before we proceed, make sure you have SQLalchemy and Pandas installed. If not, you can install both packages using the following command:

1
pip install SQLAlchemy pandas

Step 1: Create a Connection

To interact with an SQLite database file in Python, we first need to establish a connection with the file using the sqlite3 module. Here’s how to create a connection:

1
2
3
4
import sqlite3
 
# Replace 'database_file.db' with the path to your SQLite database file
conn = sqlite3.connect('database_file.db')

Step 2: Import Database using pandas module

Once connected to our database, we can use Pandas to import the table data. We can run a SQL query using pandas.read_sql_query() function which accepts the SQL query and the established connection object as arguments. For example, let’s say you have a table called ‘students’, and you want to import it into a Pandas DataFrame.

1
2
3
4
5
6
7
import pandas as pd
 
# SQL query used to fetch the data from the 'students' table
sql_query = 'SELECT * FROM students;'
 
# Run the SQL query and import the data into a Pandas DataFrame
students_df = pd.read_sql_query(sql_query, conn)

The students_df DataFrame now contains the entire ‘students’ table from the SQLite database file. Now you can use Pandas to analyze or manipulate the data as needed.

Step 3: Perform Operations and Updates (Optional)

You can use the imported data to perform various calculations, create visualizations, or manipulate the data. If you want to update the database based on your analysis, you can use the sqlite3 module to execute an SQL query.

1
2
3
4
5
# Update the 'age' column to add one year to every student
students_df['age'] = students_df['age'] + 1
 
# Update the 'students' table with the modified DataFrame
students_df.to_sql('students', conn, if_exists='replace', index=False)

Note: The if_exists parameter is useful when dealing with existing tables. if_exists='replace' replaces the existing table with the modified DataFrame, while if_exists='append' adds new rows to the existing table.

Step 4: Close the Connection

Once you have performed all the necessary operations, it is essential to close the connection to the database to prevent any data corruption or security issues.

1
conn.close()

Full code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import sqlite3
import pandas as pd
 
# 1. Create a connection
conn = sqlite3.connect('database_file.db')
 
# Create the 'students' table
create_table_query = '''
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER
    );
'''
conn.execute(create_table_query)
 
# 2. Import database using pandas
sql_query = 'SELECT * FROM students;'
students_df = pd.read_sql_query(sql_query, conn)
 
# 3. Perform Operations and Updates (Optional)
students_df['age'] = students_df['age'] + 1
students_df.to_sql('students', conn, if_exists='replace', index=False)
 
print(students_df)
 
# 4. Close the connection
conn.close()

Conclusion

In this tutorial, you’ve learned how to import an SQLite database file in Python using the built-in sqlite3 module, and how to work with the data using the Pandas library. This method allows you to quickly access and process data in SQLite databases, take advantage of powerful pandas manipulation tools and update the database when necessary.