In this article we want to learn about Python Database Connectivity, so as we know that Python is one the best and most popular programming languages, and there are a lot of libraries that you can them for database connection in Python,
In this article we want to talk about the different options that are available for connecting Python to databases and how to use them effectively.
-
Python Database API (DB-API)
Python Database API (DB-API) is a specification for connecting Python to databases. it defines a set of methods and conventions for database connectivity, and this makes it easier to write Python code that can work with any database that supports the API. most popular databases like MySQL, PostgreSQL, Oracle and SQLite have Python drivers that implement the DB-API. these drivers provide a consistent interface for Python programmers to interact with databases.
-
SQLAlchemy
SQLAlchemy is popular Python library that provides an Object Relational Mapping or ORM framework. ORM allows developers to work with databases using Python objects, making it easier to perform database operations without needing to write raw SQL queries.
SQLAlchemy supports multiple databases, including MySQL, PostgreSQL, SQLite, Oracle, and Microsoft SQL Server. It provides a range of tools and features, such as query building, connection pooling, and database schema management.
-
PyMySQL
PyMySQL is pure Python MySQL client library that provides an easy interface for connecting and interacting with MySQL databases. it is compatible with Python 2 and 3 and supports all the standard MySQL features including transactions, stored procedures and SSL encryption. PyMySQL is lightweight and fast, and it is a good choice for applications that require high performance.
-
cx_Oracle
cx_Oracle is Python extension module that enables Python programs to access Oracle databases. it provides fast and efficient interface for accessing Oracle databases, with support for all the standard Oracle features including transactions, stored procedures and object types. cx_Oracle supports both Python 2 and 3.
These are some examples of how to connect Python to different databases.
- Connecting to a MySQL database using PyMySQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
import pymysql # Open database connection db = pymysql.connect(host="localhost",user="root",password="",database="mydatabase") # Prepare a cursor object cursor = db.cursor() # Execute SQL query cursor.execute("SELECT * FROM book") # Fetch all rows rows = cursor.fetchall() for row in rows: print("ID : " + str(row[0])) print("Title : " + row[1]) print("Author : " + row[2]) print("Price : " + str(row[3])) # Close database connection db.close() |
This will be the result
- Connecting to an Oracle database using cx_Oracle:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import cx_Oracle # establish a connection dsn = cx_Oracle.makedsn(host="localhost", port=1521, sid="XE") connection = cx_Oracle.connect(user="user", password="password", dsn=dsn) # prepare a cursor cursor = connection.cursor() # execute SQL query cursor.execute("SELECT * FROM employees") # Fetch all rows rows = cursor.fetchall() # print the results for row in rows: print(row) # Close the cursor and connection cursor.close() connection.close() |
- Using SQLAlchemy to interact with a SQLite database:
|
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 29 30 31 32 33 34 |
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData # Create an engine for database engine = create_engine('sqlite:///employee.db', echo=True) # Define metadata object for database schema metadata = MetaData() # Define table users = Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String), Column('email', String), ) # Create table in the database metadata.create_all(engine) # Insert row conn = engine.connect() conn.execute(users.insert(), [ {'name': 'Geekscoders', 'email': 'admin@geekscoders.com'}, {'name': 'Parwiz', 'email': 'parwiz@geekscoders.com'}, ]) # Query the table result = conn.execute(users.select()) # Print the results for row in result: print(row) # Close the connection conn.close() |
This code first will create a database, and after that, it inserts data into the database, this will be the result.
