Python for Databases

Python Database Libraries

We shall be looking at available libraries that allow python to talk to a database.

Relational Databases

Relational Databases store data in tables that are joined back together using Structured Query Language(SQL). There are many python libraries available including:

MySQL/MariaDB
PostgreSQL
SQL Server
Oracle
DB2

NoSQL Databases

NoSQL Databases store semi-structured data and may or may not have a query language. There are many python libraries available including:

mongoDB
Apache CouchDB/IBM Cloudant
Redis

Installing Database libraries

PIP is a package installer that makes it easy to install 3rd party Python libraries. This is shown below:

python -m pip install mysql-connector-python

python -mpip install pymongo

MySQL Connection Code

MySQL can be connected with python using the following code:


import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername"
    password="yourpassword"

)

Mongo Connection Code

MongoDB can be connected with python using the following code:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]

Querying data

Data in the database can be queried with python.The standard query language for DBMS use SQL queries to retrieve data according to some criteria. For example, if we want to retrieve name, phone, and email for customers in a specific zip code, we do:

 SELECT name, phone, email FROM customers where zipcode = ‘02176’

SQL Through Python

The following code snippet shows how to run SQL commands through python:

mycursor = mydb.cursor();

mycursor.execute("SELECT * FROM customers)

myresult = mycursor.fetchall();

for x in myresult:
   print(x)

MongoDB Collections

MongoDB stores documents in collections. Collections are analogous to tables in relational databases.


{
    name: "John Smith",
    address:"24 Topland Rd",
    city:"Melrose",
    state:"Glasgow"
    postcode:"02916"
}

Query MongoDB Through Python

Here is an example of a way to query MongoDB using python for a collection called Friends:

mycoll = mydb["Friends"]

myquery = {"address" : "46 upland Rd"}

mydoc = mycoll.find(myquery)

for x in mydoc:
     print(x)

Mutating Data in the database

The data in the databases can be mutated using python.

Inserting Facts into MySQL database

The following code snippets below show how to insert data into MySQL using python:

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name,address) VALUES (%s, %s)"

val = (“Aspen", “46 Upland Rd")

mycursor.execute(sql, val)

mydb.commit()

Updating Facts into MySQL database using python

Python can be used to run SQL queries to update facts into the MySQL database as shown below:

 mycursor = mydb.cursor()

 sql = “UPDATE customers SET address = ‘46 Upland Rd’ where name = ‘Aspen’")

mycursor.execute(sql)

mydb.commit()

Deleting Facts in MySQL database using python

The following code snippets show how to delete data from the MySQL database using a query run through python:

mycursor = mydb.cursor()

sql = “delete from customers where name = ‘Doe’")

mycursor.execute(sql)

mydb.commit()

Similary, the same operations can be done in mongoDB using python.

Inserting Facts into MongoDB

Data can be inserted in MongoDB through python as shown in the following code snippets.


mydict = { "name": “Luma", "address": “46 Upland Rd" }

x = mycoll.insert_one(mydict)

Updating Data into MongoDB

Data can be updated in MongoDB through python as shown in the following code snippets.


myquery = { “name": “Tela" }

newvalues = { "$set": { "address": “40 downTown" } }

mycoll.update_one(myquery, newvalues)

Deleting Facts into MongoDB

Data can be deleted from MongoDB through python as shown in the following code snippets.

myquery = { “name": “Luma" }

mycoll.delete_one(myquery)

Summary

In summary, relational databases store data in tables and we issue SQL through python. No SQL databases have non-standard query methods which we used through python. SQL is a standard query language for relational databases. A cursor in python allows us to navigate through the result set a tuple at a time. It is necessary to recall that a collection in MongoDB is like a table. We also discovered that data or facts are stored in the database. SQL has Insert, Update & Delete statements and Mongo has functions for each operation.

SoftTrend Blogs