MySQL – Connect DB with Python mysql.connector

MySQL Connector/Python enables Python programs to access MySQL databases, using an API that is compliant with the Python Database API Specification v2.0 (PEP 249). It is written in pure Python and does not have any dependencies except for the Python Standard Library.

Syntax

>>> import mysql.connector
>>> connection = mysql.connector.connect(host="localhost", user="myusername", password="mypassword", database="mydatabase")

Example

>>> import mysql.connector
>>> 
>>> connection = mysql.connector.connect(host="localhost", user="sathiyan", password="mysql", database="mysql_db")
>>> cursor = connection.cursor()
>>> query = ("SELECT name FROM res_partner")
>>> query_execute = cursor.execute(query)
>>> rows = cursor.fetchall()
>>> for row in rows:
...     print(row[0])
...
"ABC Pvt. Ltd."
"A-Z Pvt. Ltd."
"Y! Ltd."
>>> cursor.close()
>>> connection.close()

Reference

PostgreSQL – Connect DB with Python psycopg2

Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Many Python types are supported out-of-the-box and adapted to matching PostgreSQL data types; adaptation can be extended and customized thanks to a flexible objects adaptation system.

Syntax

>>> import psycopg2
>>> connection = psycopg2.connect(host="hostname_or_ip",database="db_name", user="user_name", password="password")

Example

>>> import psycopg2
>>>
>>> connection = psycopg2.connect(host="localhost",database="odoo_12", user="sathiyan", password="postgres")
>>> cursor = connection.cursor()
>>> query = ("SELECT name FROM res_partner")
>>> query_execute = cursor.execute(query)
>>> rows = cursor.fetchall()
>>> for row in rows:
...     print(row[0])
... 

"ABC Pvt. Ltd."
"A-Z Pvt. Ltd."
"Y! Ltd."
>>> cursor.close()
>>> connection.close()

Reference

Oracle – Connect DB with Python SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

Syntax

import sqlalchemy as sa
engine_type_1 = sa.create_engine('dialect+driver://user:password@ip:port/db')
engine_type_2 = sa.create_engine('oracle://user:password@ip:port/sidname')

Example

>>> import sqlalchemy as sa
>>> engine_type_1 = sa.create_engine('oracle+cx_oracle://hr:open@localhost:1521/XE')
>>> engine_type_2 = sa.create_engine('oracle://hr:open@localhost:1521/XE')
>>> rows_type_1 = engine_type_1.execute("SELECT first_name, last_name FROM emps_pd")
>>> rows_type_2 = engine_type_2.execute("SELECT first_name, last_name FROM emps_pd")
>>> for row in rows_type_1:
...     print(row[0], row[1])
...
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
>>> for row in rows_type_2:
...     print(row[0], row[1])
...
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst

Reference

Oracle – Basic Data Export and Import Using Python cx_Oracle & Pandas

Export Data To CSV Using Pandas

>>> import cx_Oracle as cxo
>>> import pandas as pd
>>> import os
>>> connection = cxo.connect('hr/open@localhost:1521/XE')
>>> cursor = connection.cursor()
>>> query = ("SELECT * FROM employees")
>>> pd_data_frame = pd.read_sql(query, con=connection)
>>> pd_data_frame.to_csv('pd_employees.csv')
>>> os.getcwd()
'C:\\Users\\sathiyan'
>>> file_path = r"C:\Users\sathiyan\Documents\Test\pd_employees_index.csv"
>>> pd_data_frame.to_csv(file_path)
>>> pd_data_frame.to_csv(file_path, index_label='pd_seq', encoding="utf-8")
>>> file_path = r"C:\Users\sathiyan\Documents\Test\pd_employees.csv"
>>> pd_data_frame.to_csv(file_path, index=False, encoding="utf-8")
>>> cursor.close()
>>> connection.close()

Import Data From CSV Using Pandas

>>> import cx_Oracle as cxo
>>> import pandas as pd
>>> connection = cxo.connect('hr/open@localhost:1521/XE')
>>> cursor = connection.cursor()
>>> query = ("CREATE TABLE emps_pd AS (SELECT * FROM employees WHERE 1=2)")
>>> query_execute = cursor.execute(query)
>>> file_path = r"C:\Users\sathiyan\Documents\Test\pd_employees.csv"
>>> pd_data_frame = pd.read_csv(file_path, na_filter=False, parse_dates=['HIRE_DATE'])
>>> rows = [tuple(x) for x in pd_data_frame.values]
>>> rows_column_val = pd_data_frame.shape
>>> bind_var = ''
>>> for i in range(1, rows_column_val[1]+1):
...     val = ':'+str(i)
...     bind_var += val + ', '
...
>>> bind_var = bind_var[:-2]
>>> query = ("INSERT INTO emps_pd VALUES("+bind_var+")")
>>> cursor.executemany(query, rows)
>>> connection.commit()
>>> cursor.close()
>>> connection.close()

Notes

You can specify a column that contains dates so pandas would automatically parse them when reading from the csv.

pandas.read_csv('data_file.csv', parse_dates=['date_column'])

If you are reading the dataframe from a CSV, then use following to remove NaN

df.read_csv(path, na_filter=False)

If you already have the dataframe, then use following to remove NaN

df = df.replace(pd.np.nan, '', regex=True)

To remove the whole row containing NaN,

pd_data_frame = pd_data_frame.dropna()

Reference

Oracle – Connect DB with Python cx_Oracle

It is a Python extension module that enables access to Oracle Database.
You can use cx_Oracle with Oracle 11.2, 12.1 and 12.2 and 18.3 client libraries. Oracle’s standard client-server version interoperability allows connection to both older and newer databases. For example Oracle 18.3 client libraries can connect to Oracle Database 11.2.

  • The cx_Oracle project is open source and maintained by Oracle.
  • SQL and PL/SQL Execution.
  • Oracle data type support, including large object support (CLOB and BLOB).
  • Direct binding to SQL objects.

Syntax

>>> import cx_Oracle
>>> connection = cx_Oracle.connect('userid/password@IP:PORT/SID')

Example

# Python 3.7.2
>>> import cx_Oracle as cxo
>>> connection = cxo.connect('hr/open@localhost:1521/XE')
>>> cursor = connection.cursor()
>>> query = ("SELECT first_name, last_name FROM hr.employees")
>>> query_execute = cursor.execute(query)
>>> rows = query_execute.fetchall()
>>> for row in rows:
...     print(row[0], row[1])
...
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
>>> cxo.version
'7.0.0'
>>> connection.dsn
'localhost:1521/XE'
>>> connection.version
'11.2.0.2.0'
>>> cursor.close()
>>> connection.close()

Reference

Oracle – Connect DB with Python ODBC

Open Database Connectivity (ODBC) is an open standard application programming interface (API) that allows application programmers to access any database.

We can use the following python modules for Oracle DB connections.

  • pyodbc
  • cx_Oracle (We see about this in separate post in near future)
  • SQLAlchemy

Python ODBC Connect

In Windows 10 to find the Oracle Driver name

  • Open the ODBC Data Sources
  • Click the Drivers tab and search for Oracle Driver

Syntax

pyodbc.connect('DRIVER={Oracle in XE};DBQ=x.x.x.x:1521/db_name;UID=myuid;PWD=mypwd')

# DRIVER = Oracle Driver Name
# x.x.x.x = Database Server IP address (In my case localhost)
# db_name = Database Name
# myuid = Database Username
# PWD = Database Username's password

Example

>>> import pyodbc
>>> connection = pyodbc.connect('Driver={Oracle in XE};DBQ=localhost:1521/XE;Uid=hr;Pwd=open')
>>> cursor = connection.cursor()
>>> query = ("SELECT first_name, last_name FROM hr.employees")
>>> query_execute = cursor.execute(query)
>>> rows = query_execute.fetchall()
>>> for row in rows:
...     print(row)
...
('Ellen', 'Abel')
('Sundar', 'Ande')
('Mozhe', 'Atkinson')
('David', 'Austin')
('Hermann', 'Baer')
('Shelli', 'Baida')
('Amit', 'Banda')
>>> for row in rows:
...     print(row[0], row[1])
...
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda

Reference

Python Local Time to UTC Time

Convert Local datetime to UTC datetime string.

>>> from datetime import *
>>> from dateutil import *
>>> from dateutil import tz
>>> 
>>> local_time_str = "2019-01-29 05:35:35"
>>>
>>> def local_to_utc(local_tz_str, local_time_str):
...     utc_zone = tz.gettz('UTC')
...     local_zone = tz.gettz(local_tz_str)
...     # local_time = datetime.now()
...     # In case local time derived form any other file.
...     local_time = datetime.strptime(local_time_str, '%Y-%m-%d %H:%M:%S') 
...     local_time = local_time.replace(tzinfo=local_zone)
...     utc_time = local_time.astimezone(utc_zone)
...     utc_string = utc_time.strftime('%Y-%m-%d %H:%M:%S')
...     return utc_string
... 
>>> local_to_utc('Asia/Kolkata', local_time_str)
'2019-01-29 00:05:35'
>>> 

Python Set

A set is an unordered collection of items. Every element is unique (no duplicates) and must be immutable (which cannot be changed).

However, the set itself is mutable. We can add or remove items from it.

Sets can be used to perform mathematical set operations like union, intersection, symmetric difference etc.

>>> a = [1,2,3,5,6,8,10,12,14] 
>>> b = [1,3,5,6,7,8,9,11,13,15] 
>>> c = [11,12,13,14,15,1,3,4] 

# Union 
>>> union = set(a)|set(b) 
>>> union 
set([1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]) 

# Intersection 
>>> intersection = set(a)&set(b) 
>>> intersection 
set([8, 1, 3, 5, 6]) 

# Difference 
>>> difference = set(a)-set(b) 
>>> difference 
set([2, 12, 10, 14]) 
>>> difference_b = set(b)-set(a) 
>>> difference_b 
set([15, 9, 11, 13, 7]) 

# Symmetric Difference 
>>> symmetric_diff = set(a)^set(b) 
>>> symmetric_diff 
set([2, 7, 9, 10, 11, 12, 13, 14, 15]) 

# Assigning Set to Variable 
>>> set_a = set(a) 
>>> set_b = set(b) 
>>> set_c = set(c) 

# Checking Values in Set 
>>> 10 in set_a 
True 
>>> 9 in set_a 
False 
>>> 9 not in set_a 
True 

# Arithmetic Operations 
>>> set_a < set_b 
False 
>>> set_a > set_b 
False 
>>> set_a == set_b 
False 
>>> set_a <= set_b 
False 
>>> set_a >= set_b 
False 

# For Loop 
>>> for value_a in set_a: 
...    print 'True' 
... 
True 
True 
True 
True 
True 
True 
True 
True 
True 

# Length Function  
>>> len(set_a) 
9 
>>> len(set_b)
10 

# Minimum 
>>> min(set_a) 
1 

# Maximum 
>>> max(set_a) 
14 

# Copy 
>>> set_d = set_c.copy() 
>>> set_d 
set([1, 3, 4, 11, 12, 13, 14, 15]) 

# Discard & Remove 
>>> set_d.discard(15) 
>>> set_d 
set([1, 3, 4, 11, 12, 13, 14]) 
>>> set_d.remove(11) 
>>> set_d 
set([1, 3, 4, 12, 13, 14]) 
>>> set_d.remove(11) 
Traceback (most recent call last):  
File "<stdin>", line 1, in <module> 
KeyError: 11 

# Add Values to Set 
>>> set_d.add(11) 
>>> set_d 
set([1, 3, 4, 11, 12, 13, 14]) 

# Update Set With Other Set 
>>> set_d.update(set_c) 
>>> set_d 
set([1, 3, 4, 11, 12, 13, 14, 15])

Python – Age Calculator & Experience

# Age
# ===========
>>> from datetime import date, timedelta
>>> from dateutil.relativedelta import relativedelta
>>> today_date = date.today()
>>> dob = date(1987, 11, 1)
>>> today_date
datetime.date(2017, 10, 14)
>>> dob
datetime.date(1987, 11, 1)
>>> age = relativedelta(today_date, dob)
>>> age
relativedelta(years=+29, months=+11, days=+13)

# Experience
# ===========
>>> doj1 = date(2010, 5, 12)  # First DOJ
>>> left1 = date(2012, 8, 8) # Left
>>> doj2 = date(2012, 9, 1) # Rejoin
>>> left2 = date(2014, 11, 19) # Left Again
>>> doj_c = date(2015, 1, 2) # Rejoin Again
>>> today_date = date.today()
>>> exp1 = relativedelta(doj1, left1)
>>> exp1
relativedelta(years=-2, months=-2, days=-27)
>>> exp1 = relativedelta(left1, doj1)
>>> exp2 = relativedelta(left2, doj2)
>>> exp_c = relativedelta(today_date, doj_c)
>>> exp1
relativedelta(years=+2, months=+2, days=+27)
>>> exp2
relativedelta(years=+2, months=+2, days=+18)
>>> exp_c
relativedelta(years=+2, months=+9, days=+12)
>>> exp = exp1+exp2+exp_c
>>> exp
relativedelta(years=+7, months=+1, days=+57)
>>> exp_tuple = (exp.years, exp.months, exp.days)

Python – Currency Rate Web Scraping

For obtaining the XPath of elements such as FireBug for Firefox or the Chrome Inspector. If you’re using Chrome, you can right click an element, choose ‘Inspect element’, highlight the code, right click again and choose ‘Copy XPath’.

>>> from lxml import html
>>> import requests
>>> to_codes_list = ['USD', 'EUR', 'SGD', 'GBP']
>>> for code in range(len(to_codes_list)):
...     to_code = to_codes_list[code]
...     link="https://www.google.com/finance/converter?a=1&from=INR&to="+to_code+"&meta=ei%3DKlaEWYHzLdOouAS4h7_QAw"
...     page = requests.get(link)
...     tree = html.fromstring(page.content)
...     from_code_val = tree.xpath('//div[@id="currency_converter_result"]/text()')
...     currency_rate_val = tree.xpath('//*[@id="currency_converter_result"]/span/text()')
...     from_code = from_code_val[0]
...     currency_rate = currency_rate_val[0]
...     val_str = from_code+currency_rate
...     print val_str
...
 1 INR = 0.0157 USD
 1 INR = 0.0133 EUR
 1 INR = 0.0214 SGD
 1 INR = 0.0120 GBP
>>> val_str = '1 INR = 0.0157 USD'
>>> val_str
'1 INR = 0.0157 USD'
>>> val_str.split(' ')
['1', 'INR', '=', '0.0157', 'USD']
>>> rate = val_str.split(' ')
>>> rate[3]
'0.0157'
>>>