In this article we want to learn about Python Data Retrieval from Databases, so we know that Python is an excellent language for data retrieval from databases, because it has different libraries and modules and it makes it easy to connect to databases and extract data. in this article we want to talk about some of the most popular libraries and methods for data retrieval from databases using Python.
Python Data Retrieval from Databases
First step in retrieving data from a database in Python is to connect to the database. this can be done using different libraries.
- SQLite3: SQLite3 is builtin library in Python that provides lightweight database that can be accessed without separate server process. SQLite3 is particularly useful for small to medium sized applications and data sets. for connecting to an SQLite database you can use this code:
|
1 2 3 |
import sqlite3 conn = sqlite3.connect('database_name.db') |
- MySQL Connector/Python: MySQL Connector is a library that allows you to connect Python to MySQL databases. for using this library we need to install this library. we can install it using this command:
|
1 |
pip install mysql-connector-python |
After installation you can use this code for connecting to MySQL database:
|
1 2 3 4 5 6 7 8 |
import mysql.connector conn = mysql.connector.connect( host="hostname", user="username", password="password", database="database_name" ) |
- SQLAlchemy: SQLAlchemy is popular library for working with SQL databases in Python. it provides an ORM Object Relational Mapping system that allows you to work with databases using Python classes and objects. for connecting to a database using SQLAlchemy, you can use this code:
|
1 2 3 |
from sqlalchemy import create_engine engine = create_engine('database://user:password@host:port/database_name') |
After that you have connected to a database, you can start extracting data from it using SQL queries. you can use execute() method of the connection object to execute SQL queries. for example to retrieve all rows from a table, you can use this code:
|
1 2 3 |
cursor = conn.cursor() cursor.execute('SELECT * FROM table_name') rows = cursor.fetchall() |
If you want to retrieve specific columns from a table, you can modify the SQL query like this:
|
1 2 |
cursor.execute('SELECT column1, column2 FROM table_name') rows = cursor.fetchall() |
After that we have retrieved the data from the database, we can work with it in Python using different libraries such as Pandas and NumPy. for example in here we are converting the retrieved data into Pandas DataFrame.
|
1 2 3 |
import pandas as pd df = pd.DataFrame(rows, columns=['column1', 'column2']) |
This is the complete code for this article
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
import mysql.connector import pandas as pd # Connect to the database conn = mysql.connector.connect( host="localhost", user="root", password="", database="mydatabase" ) # Execute SQL query and retrieve data cursor = conn.cursor() cursor.execute("SELECT * FROM book") rows = cursor.fetchall() # Convert retrieved data into a Pandas DataFrame df = pd.DataFrame(rows, columns=['ID', 'Name', 'Author', 'Price']) # Print DataFrame print(df) # Close database connection conn.close() |
Run the code and this will be the result
