Python inbuild SQL DB

If you're working with data in Python, you might need to store it in a database for easy access and manipulation. One popular database management system is SQLite, which is a lightweight and self-contained library that can be easily integrated into Python. In this article, we'll walk through the steps to install and use SQLite in Python.

Installing SQLite in Python

The first step is to install the SQLite library in your Python environment. You can do this using pip, which is the package installer for Python. Open your Python notebook or terminal and type the following command:

!pip install sqlite3         

This command will install the SQLite library and its dependencies in your environment.

Creating a SQLite Database in Python

Once you've installed the SQLite library, you can create a new database and table using Python. Here's an example code snippet:

import sqlite


# Connect to the database
conn = sqlite3.connect('mydatabase.db')


# Create a cursor object
c = conn.cursor()


# Create a new table
c.execute('''CREATE TABLE stocks
? ? ? ? ? ? ?(date text, trans text, symbol text, qty real, price real)''')


# Insert some data into the table
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("INSERT INTO stocks VALUES ('2006-03-28','BUY','IBM',1000,45.0)")


# Commit the changes
conn.commit()


# Close the cursor and connection
c.close()
conn.close()

        

In this code snippet, we first connect to the SQLite database using the connect() method from the sqlite3 module. Then, we create a cursor object using the cursor() method. We use the cursor to execute a CREATE TABLE statement that creates a new table named stocks with five columns. We then insert some data into the table using the INSERT INTO statement.

After inserting the data, we call the commit() method to save the changes to the database. Finally, we close the cursor and connection using the close() method.

Retrieving Data from a SQLite Database in Python

Now that we've created a SQLite database in Python, we can retrieve data from it using the SELECT statement. Here's an example code snippet:

import sqlite

# Connect to the database
conn = sqlite3.connect('mydatabase.db')

# Create a cursor object
c = conn.cursor()

# Execute a SELECT statement
c.execute('SELECT * FROM stocks')

# Fetch all the rows from the result set
rows = c.fetchall()

# Print the rows
for row in rows:
    print(row)

# Close the cursor and connection
c.close()
conn.close()        

In this code snippet, we use the same connect() and cursor() methods as before. We execute a SELECT statement that retrieves all the rows from the stocks table using the execute() method. We then use the fetchall() method to fetch all the rows from the result set and store them in the rows variable. Finally, we loop through the rows variable and print each row.

You can modify the SELECT statement to retrieve specific columns or filter the results using WHERE clauses. The fetchone() method can be used to retrieve a single row from the result set.

Conclusion

In this article, we've shown you how to install and use SQLite in Python. SQLite is a great database management system for small to medium-sized projects because it's lightweight and easy to use. You can use it to store and manipulate