Connecting Databases using Python.ipynb

Kernel: Python 3

Connecting to Database

The Python standard for database interfaces is the Python DB-API.In most of the cases one have download a separate DB API module for each database access.

The DB API provides a minimal standard for working with databases using Python structures and syntax wherever possible. This API includes the following −

• Importing the API module.

• Acquiring a connection with the database.

• Issuing SQL statements and stored procedures.

• Closing the connection

1. Database Connection: SQlite3

What is SQLite?

SQLite is a relational database management system (In C) ,that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.

• SQLite3 can be integrated with Python using sqlite3 module

• No need to install as it shipped by default along with Python version 2.5.x onwards.

• To use sqlite3 module, you must first create a connection object that represents the database and then optionally you can create a cursor object, which will help you in executing all the SQL statements.

• conda install -c blaze sqlite3

Important APIs

sqlite3.connect(database [,timeout ,other optional arguments])

• This API opens a connection to the SQLite database file. You can use ":memory:" to open a database connection to a database that resides in RAM instead of on disk. If database is opened successfully, it returns a connection object.

connection.cursor([cursorClass])

• This routine creates a cursor which will be used throughout of your database programming with Python. This method accepts a single optional parameter cursorClass.

cursor.execute(sql [, optional parameters])

• This routine executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks and named placeholders (named style).

  For example − cursor.execute("insert into name values (?, ?)", (who, income))

connection.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.

connection.rollback()

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

# Program to Connect to the exixting database
##If the database does not exist, then it will be created and finally a database object will be returned
import sqlite3

conn = sqlite3.connect('D1.db')

print ("My first Connection")

My first Connection

# Program to create a table in the previously created database
conn = sqlite3.connect('D1.db')
print ("Opened database successfully")

conn.execute('''CREATE TABLE COMPANY1
         (ID INT PRIMARY KEY     NOT NULL,
         NAME           TEXT    NOT NULL,
         AGE            INT     NOT NULL,
         ADDRESS        CHAR(50),
         SALARY         REAL);''')
print ("Table created......")

conn.close()

Opened database successfully
Table created......

## to create records in the COMPANY table created in the above example.
conn = sqlite3.connect('D1.db')
print ("Opened database successfully");

conn.execute("INSERT INTO COMPANY1 (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (7, 'Poy', 30, 'UP', 30000.00 )")

conn.execute("INSERT INTO COMPANY1 (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (8, 'Ram', 33, 'FARIDABAD', 18000.00 )")

conn.execute("INSERT INTO COMPANY1 (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (9, 'edd', 42, 'NEW DELHI', 22000.00 )")

conn.commit()
print ("Records updated successfully");
conn.close()

Opened database successfully
Records updated successfully

## how to fetch and display records from the COMPANY table created
conn = sqlite3.connect('D1.db')
print ("Opened database successfully");

cursor = conn.execute("SELECT id, name, address, salary from COMPANY1")
for row in cursor:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n") 

print ("Record fetched");
conn.close()

Opened database successfully
ID =  7
NAME =  Poy
ADDRESS =  UP
SALARY =  30000.0 

ID =  8
NAME =  Ram
ADDRESS =  FARIDABAD
SALARY =  18000.0 

ID =  9
NAME =  edd
ADDRESS =  NEW DELHI
SALARY =  22000.0 

Record fetched

import pandas as pd
import sqlite3
conn = sqlite3.connect("D1.db")
SQL_Q = pd.read_sql_query(
'''SELECT id, name, address, salary from COMPANY1''', conn)

d = pd.DataFrame(SQL_Q, columns=['ID','NAME','ADDRESS','SALARY'])
d

## Update & Delete
conn = sqlite3.connect('D1.db')
print ("Opened database successfully");
conn.execute("UPDATE COMPANY1 set SALARY = 25000.00 where ID = 1")## Updation
conn.execute("DELETE from COMPANY1 where ID = 2;")# Delete
conn.commit
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print ("Updated Record fetched");
conn.close()

Opened database successfully
ID =  7
NAME =  Poy
ADDRESS =  UP
SALARY =  30000.0 

ID =  8
NAME =  Ram
ADDRESS =  FARIDABAD
SALARY =  18000.0 

ID =  9
NAME =  edd
ADDRESS =  NEW DELHI
SALARY =  22000.0 

Updated Record fetched

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
 
def query_with_fetchone():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM books")
 
        row = cursor.fetchone()
 
        while row is not None:
            print(row)
            row = cursor.fetchone()
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()
 
 
if __name__ == '__main__':
    query_with_fetchone()

• First, we connected to the database by create a new MySQLConnection object

• Second, from the MySQLConnection object, we instantiated a new MySQLCursor object

• Third, we executed a query that selects all rows from the books table.

• Fourth, we called fetchone() method to fetch the next row in the result set. In the while loop block, we printed out the content of the row and move to the next row until all rows are fetched.

• Fifth, we closed both cursor and connection objects by invoking the close() method of the corresponding object.

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
def insert_book(title, isbn):
    query = "INSERT INTO books(title,isbn) " \
            "VALUES(%s,%s)"
    args = (title, isbn)
 
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
 
        cursor = conn.cursor()
        cursor.execute(query, args)
 
        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')
 
        conn.commit()
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()
 
def main():
   insert_book('A Sudden Light','9781439187036')
 
if __name__ == '__main__':
    main()

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
def insert_books(books):
    query = "INSERT INTO books(title,isbn) " \
            "VALUES(%s,%s)"
 
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
 
        cursor = conn.cursor()
        cursor.executemany(query, books)
 
        conn.commit()
    except Error as e:
        print('Error:', e)
 
    finally:
        cursor.close()
        conn.close()
 
def main():
    books = [('Harry Potter And The Order Of The Phoenix', '9780439358071'),
             ('Gone with the Wind', '9780446675536'),
             ('Pride and Prejudice (Modern Library Classics)', '9780679783268')]
    insert_books(books)
 
if __name__ == '__main__':
    main()

##DELIMITER $$

##USE python_mysql$$

CREATE PROCEDURE find_all() BEGIN SELECT title, isbn, CONCAT(first_name,' ',last_name) AS author FROM books INNER JOIN book_author ON book_author.book_id = books.id INNER JOIN AUTHORS ON book_author.author_id = authors.id; END$$

DELIMITER ;

## Deletion
from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
def delete_book(book_id):
    db_config = read_db_config()
 
    query = "DELETE FROM books WHERE id = %s"
 
    try:
        # connect to the database server
        conn = MySQLConnection(**db_config)
 
        # execute the query
        cursor = conn.cursor()
        cursor.execute(query, (book_id,))
 
        # accept the change
        conn.commit()
 
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()
 
if __name__ == '__main__':
    delete_book(102)

2. Database Connection : MongoDB

MongoDB is a cross-platform document-oriented database program. Classified as a NoSQL database program, MongoDB uses JSON-like documents with schemata. (Key : Value)

Why MongoDB?

• The ever-increasing need for the ability to store complex data structures led to the birth of NoSQL databases, which allow a developer to store heterogeneous and structure-less data.

• MongoDB also provides load balancing support by automatically moving data across the various shards

• It has automatic failover support - in case your primary server goes down, a new primary will be up and running automatically

• The MongoDB Management Service or MMS is a very nice web tool that provides you with the ability to track your machines

• Deployment of MongoDB is very easy

To install this package with conda run

conda install -c anaconda pymongo

link to install MongoDB in windows

https://medium.com/@LondonAppBrewery/how-to-download-install-mongodb-on-windows-4ee4b3493514

import pymongo

from pymongo import MongoClient 

# Creating Connection
from pymongo import MongoClient 
client = MongoClient() 

# connect the MongoClient on the localhost which runs on port number 27017
# client = MongoClient(‘host’, port_number) 
client = MongoClient("localhost", 27017)

# Access DataBase Objects 
mydatabase = client["MongoDB"]

## Accessing the Collection : Collections are equivalent to Tables in RDBMS. 
## We access a collection in PyMongo in the same way as we access the Tables in the RDBMS.
mongodata = mydatabase["mongoTable"]

Note : MongoDB store data in following form

record = {

title: 'MongoDB and Python',

description: 'MongoDB is no SQL database',

tags: ['mongodb', 'database', 'NoSQL'],

viewers: 104 }

## Insert the data inside a collection : 
#Methods used:insert_one() or insert_many()
rec={ 
"title": 'MongoDB and Python',  
"description": 'MongoDB is no SQL database',  
"tags": ['mongodb', 'database', 'NoSQL'],  
"viewers": 104 
} 
rec = mongodata.insert_one(rec) 

# find() is used to get more than one single document as a result of query.
for i in mydatabase.mongoTable.find({"title": 'MongoDB and Python'}) :
	print(i) 

# count() is used to get the numbers of documents with the name as passed int he parameters.
print(mydatabase.mongoTable.count({"title": 'MongoDB and Python'})) 

# To print all the documents/entries inside ‘mongoTable’ of database ‘mydatabase’ 

try: 
	conn = MongoClient() 
	print("Connected successfully!!!") 
except: 
	print("Could not connect to MongoDB") 

# database name: mydatabase 
db = conn.mydatabase 

# Created or Switched to collection names: myTable 
collection = db.mongoTable 

# To find() all the entries inside collection name 'myTable' 
cursor = collection.find() 
for record in cursor: 
	print(record) 

# Python code to illustrate 
# Insert in MongoDB 
from pymongo import MongoClient 

try: 
	conn = MongoClient() 
	print("Connected successfully!!!") 
except: 
	print("Could not connect to MongoDB") 

# database 
db = conn.database 

# Created or Switched to collection names: my_gfg_collection 
collection = db.my_gfg_collection 

emp_rec1 = { 
		"name":"Naval", 
		"id":104, 
		"location":"Delhi"
		} 
emp_rec2 = { 
		"name":"Shruti", 
		"id":105, 
		"location":"Gurugoan"
		} 
emp_rec3 = { 
		"name":"Pogo", 
		"id":106, 
		"location":"Noida"
		} 

# Insert Data 
rec_id1 = collection.insert_one(emp_rec1) 
rec_id2 = collection.insert_one(emp_rec2) 
rec_id3 = collection.insert_one(emp_rec3) 
print("Data inserted with record ids",rec_id1," ",rec_id2,rec_id3) 

# Printing the data inserted 
cursor = collection.find() 
for record in cursor: 
	print(record) 

# Python code to illustrate 
# Replace_one() in MongoDB 
from pymongo import MongoClient 

try: 
	conn = MongoClient() 
	print("Connected successfully!!!") 
except: 
	print("Could not connect to MongoDB") 

# database 
db = conn.database 

# Created or Switched to collection names: my_gfg_collection 
collection = db.my_gfg_collection 

# replace one of the employee data whose name is Mr.Shaurya 
result = collection.replace_one( 
		{"name":"Mr.Shaurya"}, 
		{ 
				"name":"Roy", 
				"id":108, 
				"location":"Noida"
				
				} 
		) 

print("Data replaced with id",result) 

# Print the new record 
cursor = collection.find() 
for record in cursor: 
	print(record)