python database - ghdrako/doc_snipets GitHub Wiki

  • pymysql: A library that provides a Python interface for connecting to a MySQL database.
  • SQLite3: A library that provides a Python interface for connecting to an SQLite database.
  • psycopg2: A library that provides a Python interface for connecting to a PostgreSQL database.
  • cx_Oracle

DB-API 2.0 specification described by PEP 249

Concepts of Python DB-API

  1. Connection Objects — Database Connections and Manage Transactions

  • cursor(factory=Cursor)-The cursor method accepts a single optional parameter factory. If supplied, this must be a callable returning an instance of Cursor or its subclasses.

  • commit()-This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.

  • rollback()-This method rolls back any changes to the database since the last call to commit().

  • close()-This closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost!

  1. Cursor Objects — Database Queries
import psycopg2  
# Connect to the PostgreSQL database  
conn = psycopg2.connect(  host="192.168.187.134",  database="mydb",  user="myusr",  password="mypassword"  )  print(" ## Connected to PostgreSQL database! MYDB ## ")  
print()  # Create a cursor object to interact with the database  
cur = conn.cursor()  
print(" ## Select dataset from sales table! ## ")  
print("----------------------------------------------")  # Execute a SQL query  
cur.execute("SELECT * FROM sales")  # Fetch the results of the 
query  rows = cur.fetchall()  # Print the results  for 
row in rows:  
  print(row)  # Close the cursor and database connection  
cur.close()  
conn.close()