Access Relation Databases with Python

Access Relation Databases with Python

To access relational databases in Python, you typically use a database adapter that conforms to the Python Database API Specification v2.0 (PEP 249). The most common adapters are sqlite3 for SQLite databases, which is included with Python, and psycopg2 for PostgreSQL, mysql-connector-python for MySQL, or pyodbc and pymssql for MS SQL Server. Each database has its own adapter.

Below are examples of how to connect to different relational databases using Python:

SQLite

SQLite comes built into Python, so you don't need to install anything extra.

import sqlite3 # Connect to an SQLite database. The database file is test.db in the current directory. conn = sqlite3.connect('test.db') # Create a cursor object using the cursor() method cursor = conn.cursor() # Create table cursor.execute('''CREATE TABLE IF NOT EXISTS example (id INTEGER PRIMARY KEY, name TEXT)''') # Insert a row of data cursor.execute("INSERT INTO example (name) VALUES ('Sample Name')") # Save (commit) the changes conn.commit() # Close the connection if done conn.close()

PostgreSQL

For PostgreSQL, you'll need to install psycopg2 or psycopg2-binary.

pip install psycopg2-binary 
import psycopg2 # Connect to a PostgreSQL database conn = psycopg2.connect(host="localhost", database="testdb", user="username", password="password") # Create a cursor object cursor = conn.cursor() # Execute a query cursor.execute("SELECT * FROM example") # Retrieve query results records = cursor.fetchall() # Close the connection conn.close()

MySQL

For MySQL, you can use mysql-connector-python.

pip install mysql-connector-python 
import mysql.connector # Connect to a MySQL database conn = mysql.connector.connect(host="localhost", database="testdb", user="username", password="password") # Create a cursor object cursor = conn.cursor() # Execute a query cursor.execute("SELECT * FROM example") # Fetch one result row = cursor.fetchone() # Close the connection conn.close()

MS SQL Server

For MS SQL Server, pyodbc is commonly used.

pip install pyodbc 
import pyodbc # Connect to an MS SQL Server database conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=username;PWD=password') # Create a cursor object cursor = conn.cursor() # Execute a query cursor.execute("SELECT * FROM example") # Fetch all results rows = cursor.fetchall() # Close the connection conn.close()

In all cases, replace "localhost", "testdb", "username", and "password" with your database's host, database name, username, and password, respectively. Also, ensure that you have proper network access to the database server and that the database exists and is configured to accept connections from your application.

Always remember to close the connection to avoid any unnecessary open connections which might lead to security risks or consume database resources. For production code, you'd typically use a context manager (with statement) to ensure that resources are properly cleaned up after use.

More Tags

graphql dynamics-crm-online uiactivityviewcontroller primefaces bluez subscribe datacontext linux-kernel cloud jekyll

More Programming Guides

Other Guides

More Programming Examples