1 of 17

Python- Database

CDSID: lvishwan

1

LEVEL -7

2 of 17

CDSID: lvishwan

2

7/12/2019

Contents

​

  • Oracle database
  • SQL database
  • Google sheet as Database

​

3 of 17

Database:

​

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS).

​

Relational databases were developed in the 1970s. Object-oriented databases came next in the 1980s. Today, we use Structured Query Language (SQL), NoSQL and cloud databases.

​

​

Hierarchical databases

Network databases

Object-oriented databases

Relational databases

Cloud Database

Centralized Database

Operational Database

NoSQL databases

​

​

​

What is a database management system?

A DBMS enables users to create and manage a database. It also helps users create, read, update and delete data in a database, and it assists with logging and auditing functions.

​

​

​

4 of 17

Major Databases Using:

​

5 of 17

MySQL

​

MySQL is a widely used relational database management system (RDBMS).

MySQL is free and open-source.

MySQL is ideal for both small and large applications

Some of The Most Important SQL Commands

  • SELECT - extracts data from a database
  • UPDATE - updates data in a database
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index

​

​

6 of 17

Where clause

Join

Distinct

json extract

​

7 of 17

PYMYSQL

​

import pymysql

​

db = pymysql.connect(host='localhost',user='root',

password='',

database='test',

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor)

cursor = db.cursor()

cursor.execute("SELECT * from test_table")

print(cursor.fetchall())

​

cursor.execute("SELECT VERSION()")

​

data = cursor.fetchone()

print ("Database version : %s " % data)

db.close()

​

8 of 17

Mongo DB

​

MongoDB is a document database. It stores data in a type of JSON format called BSON.

A record in MongoDB is a document, which is a data structure composed of key value pairs similar to the structure of JSON objects.

Records in a MongoDB database are called documents, and the field values may include numbers, strings, booleans, arrays, or even nested documents.

Example:

{

title: "Post Title 1",

body: "Body of post.",

category: "News",

likes: 1,

tags: ["news", "events"],

date: Date()

}

​

​

9 of 17

SQL vs Document Databases

SQL databases are considered relational databases. They store related data in separate tables. When data is needed, it is queried from multiple tables to join the data back together.

MongoDB is a document database which is often referred to as a non-relational database. This does not mean that relational data cannot be stored in document databases. It means that relational data is stored differently. A better way to refer to it is as a non-tabular database.

MongoDB stores data in flexible documents. Instead of having multiple tables you can simply keep all of your related data together. This makes reading your data very fast.

You can still have multiple groups of data too. In MongoDB, instead of tables these are called collections.

10 of 17

import cx_Oracle

​

connection = cx_Oracle.connect(

user="viswa",

password='123',

dsn="localhost/orcl")

​

c=connection.cursor()

c.execute("create table TABLE_1 (xyz varchar2(100))")

connection.commit()

​

c.close()

connection.close()

11 of 17

google API: google sheet as database

​

​

import gspread

gc=gspread.service_account(filename='creds.json')

sh=gc.open('database').sheet1

sh.update('A1','test')

​

​

​

​

pre request:

​

  • goto google developer
  • create new project
  • goto google drive api & enable
  • goto sheets api & enable
  • goto credential→ create credential → service account → account name → create→ done
  • Edit →add key→ json → save file in py current directory
  • add sheet and share to created account
  • Run python file

12 of 17

Workbook

  • sh = gc.create('A new spreadsheet')
  • sh.share('otto@example.com', perm_type='user', role='writer')
  • sh = gc.open("Example spreadsheet")

​

work sheet

  • worksheet = sh.get_worksheet(0)
  • worksheet_list = sh.worksheets()
  • worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")
  • sh.del_worksheet(worksheet)

​

Rows

  • sh.resize(1) Delete all rows above
  • LR=len(sh.get_all_values())
  • values_list = worksheet.row_values(1)
  • sh.append_row(['a','b'])

​

columns

  • values_list = worksheet.col_values(1)

​

cell

  • val = worksheet.cell(1, 2).value
  • worksheet.update_cell(1, 2, 'Bingo!')
  • cell form= worksheet.cell(1, 2, value_render_option='FORMULA').value

​

cells

  • list_of_lists = worksheet.get_all_values()
  • list_of_dicts = worksheet.get_all_records()

13 of 17

  • cell = worksheet.find("Dough")
  • print("Found something at R%sC%s" % (cell.row, cell.col))
  • cell_list = worksheet.findall("Rug store")
  • worksheet.batch_clear(["A1:B1", "C2:E2", "my_named_range"])
  • worksheet.clear()

​

​

Utils:

  • rowcol_to_a1(1, 1)
  • a1_to_rowcol('A1')
  • a1_range_to_grid_range('A1:A1')
  • a1_range_to_grid_range('A:B')
  • a1_range_to_grid_range('A5:B')
  • a1_range_to_grid_range('1')
  • a1_range_to_grid_range('A1', sheet_id=0)
  • is_scalar(42)

14 of 17

cell format:

​

worksheet.format("A2:B2", { "backgroundColor": { "red": 0.0, "green": 0.0, "blue": 0.0 }, "horizontalAlignment": "CENTER", "textFormat": { "foregroundColor": { "red": 1.0, "green": 1.0, "blue": 1.0 }, "fontSize": 12, "bold": True } })

​

Pandas:

import pandas as pd

dataframe = pd.DataFrame(worksheet.get_all_records())

worksheet.update([dataframe.columns.values.tolist()] + dataframe.values.tolist())

​

array:

array = np.array(worksheet.get_all_values())

array = np.array([[1, 2, 3], [4, 5, 6]])

worksheet.update('A2', array.tolist())

15 of 17

from google.oauth2 import service_account

from getfilelistpy import getfilelist

​

SCOPES = ['https://www.googleapis.com/auth/drive']

SERVICE_ACCOUNT_FILE = 'creds.json'

credentials = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)

topFolderId ='13SVxB_BMYPPjno9-GbOAkjjZ8BMbFKRv'

​

resource = {

"service_account": credentials,

"id": topFolderId,

"fields": "files(name,id)",

}

res = getfilelist.GetFileList(resource)

print(dict(res)['totalNumberOfFiles'])

​

Pre requirements:

  • generate credential json file
  • share folder
  • get folder id

Google drive API

get file list in folder

16 of 17

Upload files:

import json

import requests

headers = {"Authorization": "Bearer ya29.A0ARrdaM8lFoN3dh08BQ202enzyYNossQQxvWzigmbkiyzBSzNguAaCf3RfdfpjNzNOs85GYt_CoTXmBx8DmWr2RDVRRa9QdcU9P-PzN-62pVMMJ_xrV5J8lBSj5LBI7yFoy8j8i-uYjAhm3wqo2XvGqzD05cR"}

para = {

"name": "samplefile.png",

"parents":["13SVxB_BMYPPjno9-GbOAkjjZ8BMbFKRv"]

}

files = {

'data': ('metadata', json.dumps(para), 'application/json; charset=UTF-8'),

'file': open("D:/media/stored/3d.png", "rb")

}

r = requests.post(

"https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart",

headers=headers,

files=files

)

print(r.text)

​

prerequisite: enable Google API & get authorization key- oAuth2.0 playground

<ref:https://www.youtube.com/watch?v=JwGzHitUVcU>

​

17 of 17

Create Folder