Python has gained huge popularity in recent years due to its versatility, ease of use, and powerful data analysis and manipulation capabilities. One of the fundamental features of data analysis involves querying and manipulating data stored in databases.
Python provides libraries such as sqlite3, PyMySQL, and cx_Oracle for connecting to different databases. In this article, we will focus on creating a database in Python using sqlite3 and connect Python to different database applications.
Creating A Database in Python Using Sqlite3
Step 1: Create the Database and Tables
The first step in creating a database using sqlite3 in Python is to create the database and tables. To do this, we import the sqlite3 library and establish a connection to the database using the connect() method.
We can then create tables using SQL commands, and execute them using the execute() method of the cursor object.
import sqlite3
# creating a connection to the database
conn = sqlite3.connect('example.db')
# creating a cursor object
cursor = conn.cursor()
# creating a table
cursor.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')
#saving the changes and closing the connection
conn.commit()
conn.close()
In the code above, we connected to the database using sqlite3.connect() method and created a cursor object. We then create a table called stocks using SQL commands within the execute() method of cursor object.
The execute() method is used to execute SQL commands in the database. Finally, we use conn.commit() to save the changes made in the transaction and close the connection using conn.close().
Step 2: Insert values into the tables
Once we have created a table, we can now insert values into the table. To insert values into a table, we can use SQL commands and the execute() method of the cursor object.
# inserting values into the table
cursor.execute("INSERT INTO stocks VALUES ('21-02-2021', 'BUY', 'GOOGL', 100, 1987.75)")
# saving the changes and closing the connection
conn.commit()
conn.close()
In the code above, we insert a row containing values for each column into the stocks table. The values that we are inserting correspond to date, transaction type, the stock symbol, quantity and price.
After inserting values into the table, we again use conn.commit() to save the changes made in the transaction and then close the connection using conn.close().
Step 3: Display the results
To display the results from the database, we first retrieve the data using the SELECT statement.
We can then display the data by using Pandas DataFrame.
import pandas as pd
# connecting to the database
conn = sqlite3.connect('example.db')
# reading data from the stocks table
df = pd.read_sql_query("SELECT * from stocks", conn)
# displaying the dataframe
print(df.head())
# closing the connection
conn.close()
The above script imports the Pandas library and connects to a previously created database named example.db. Next, we retrieve all records in the stocks table using the SELECT statement together with the read_sql_query() method.
Finally, we print the first few records of the DataFrame which are then displayed on the console.
Connecting Python With Different Database Applications
Connecting Python with MySQL
MySQL is an open-source relational database that is popular for web applications. To connect Python with MySQL, we can use the PyMySQL library, which provides a Python interface to MySQL.
import pymysql
# creating a connection to the database
connection = pymysql.connect(
host="localhost",
user="root",
password="password",
db="database_name",
)
# creating a cursor object
cursor = connection.cursor()
# executing the SELECT statement
cursor.execute("SELECT * FROM table_name")
# fetching the result
result = cursor.fetchall()
In the script above, we established a connection to the MySQL server using PyMySQLs connect() method. We created a cursor object for executing commands and then executed the SELECT statement querying the required tables.
Finally, we fetch the result using cursor.fetchall() method.
Connecting Python with Oracle
Oracle is a powerful and widely used relational database management system (RDBMS). To connect Python with Oracle, we can use the cx_Oracle library, which provides a Python interface to Oracle.
import cx_Oracle
# creating a connection to the database
con = cx_Oracle.connect('username/password@host:port/service_name')
# creating a cursor object
cursor = con.cursor()
# executing the SELECT statement
cursor.execute("SELECT * FROM table_name")
# fetching the result
result = cursor.fetchall()
In the script above, we established a connection to the Oracle database using cx_Oracles connect() method. We then create a cursor object and execute the SELECT statement required.
Finally, we fetch the result using cursor.fetchall() method.
Conclusion
In this article, we looked at how to create a database in Python using sqlite3, which is a simple but powerful way to store and retrieve data. We also learned how to connect Python with different database applications such as MySQL and Oracle.
Python provides a powerful and flexible interface for manipulating data stored in databases. By using the appropriate libraries for database connections, Python remains an effective platform for data analysis and manipulation.
Overall, this article explored the process of creating a database in Python using sqlite3 and connecting Python to different database applications such as MySQL and Oracle. It emphasized the importance of storing and retrieving data, and highlighted how Python provides a powerful and flexible interface for data manipulation.
By using the appropriate libraries for database connections, Python is a valuable platform for data analysis. As a takeaway, readers can now understand how to create databases, insert values, and display results in Python, as well as connect Python to different databases.
Python continues to be a popular language for data analysis and provides numerous opportunities for individuals and businesses to extract valuable insights from stored data.