There was a problem confirming the ssl certificate : [SSL: TLSV1_ALERT_PROTOCOL_VERSION] tlsv1 alert protocol version (_ssl.c:645)

PIP Issue while installing psycopg2:

I created a virtual environment and tried to install psycopg2, but ended with the following error message:

There was a problem confirming the ssl certificate
: [SSL: TLSV1_ALERT_PROTOCOL_VERSION] tlsv1 alert protocol version (_ssl.c:645)

To overcome this issue we need to follow the below steps:

1. Check which python & its ssl version
 
python -c "import ssl; print(ssl.OPENSSL_VERSION)"
OpenSSL 1.0.2f 28 Jan 2016

python3 -c "import ssl; print (ssl.OPENSSL_VERSION)"
OpenSSL 0.9.8zh 14 Jan 2016


2. Check pip
pip --version
pip 9.0.1 from /Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-
packages (python 3.5)

3. Upgrade pip for python3
 
curl https://bootstrap.pypa.io/get-pip.py | python3
pip --version
pip 10.0.1 from /Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/pip (python 3.5)
which pip
/Library/Frameworks/Python.framework/Versions/3.5/bin/pip

This solved the issue installing psycopg2.

Python Fundamental - Operators

#Save the below code as python file and execute to see output.

varA = 15
varB = 6

# 1. Addition operator
add_sample = varA + varB
print(add_sample)

# 2. Subtract operator
sub_sample = varA - varB
print(sub_sample)

# 3. Multiply operator
multiply_sample = varA * varB
print(multiply_sample)

# 4. Division operator
division_sample = varA / varB
print(division_sample)

#5. Add Assignment (usefull for loop statement, any one below method can be used)

add_sample += 3
print(add_sample)

add_sample = add_sample + 1
print(add_sample)

# Similarly for other operators, use operator sign befor equal to assign value:
# examples:  -=, *=, /=

#7 Modulus

mod_sample = varA % varB
print(mod_sample)


#8 exponentiation

exp_sample = varA ** 2
print(exp_sample)

# Note: Operator Rule
# BODMAS: Bracket Orders Division Multiple Addition Subtraction

Python Fundamental - Strings and Indexes Example

#Save the below code as python file and execute to see output.

# 1. Normal

strA = 'My First string in quotes'
strB = "My first string in double quotes"

print (strA + "; " + strB)

#2. Escape Sequence
# escA= "My "first" double quotes" (This will result in error)
escA = "My 'first' double quotes"
escB = "My \"first\" double quotes"
print( escA + "; " + escB)

#3 String Index
# Index starts at 0 in python
indA = strA[0]
indB = strA[5]
print("Print indexes: " + indA + "; " + indB)

#4 Slicing of Strings

strC = "Python"

sliceA = strC[:3] #gives first 3 characters
sliceB = strC[3:] #gives last 3 characters
sliceC = strC[2:4] #gives 3 and 4 characters
print("Print slice indexes: " + sliceA + "; " + sliceB + "; "+ sliceC)

Python Fundamental Variables and Datatypes - Examples

#Save the below code as python file and execute to see output.

# 1. Add a variable and assign datatype int

myInt = 5
print(myInt)

# 2. Add a variable and assign datatype float

myFloat = 5.5
print(myFloat)

 # 3. Add a variable and assign datatype boolean
myBool = True
print(myBool)

Unable to install mysqlclient in python virtualenv

While installing mysqlclient in python virtualenv, some might have experienced issue like 

"failed building wheel for mysqlclient". This issue occurs mostly in Windows, to overcome this we need to download appropriate version of mysqlclient-xxx-xx.whl files, search for below files

Mysqlclient, a fork of the MySQL-python interface for the MySQL database.

• mysqlclient‑1.3.10‑cp27‑cp27m‑win32.whl
• mysqlclient‑1.3.10‑cp27‑cp27m‑win_amd64.whl
• mysqlclient‑1.3.10‑cp34‑cp34m‑win32.whl
• mysqlclient‑1.3.10‑cp34‑cp34m‑win_amd64.whl
• mysqlclient‑1.3.10‑cp35‑cp35m‑win32.whl
• mysqlclient‑1.3.10‑cp35‑cp35m‑win_amd64.whl
• mysqlclient‑1.3.10‑cp36‑cp36m‑win32.whl
• mysqlclient‑1.3.10‑cp36‑cp36m‑win_amd64.whl

and run the command

$ pip install mysqlclient-1.3.10.cp27m-win32.whl

you can see mysqlclient installed successfully.

Django Migrate Sqllite to MySql DB

Steps to migrate Django Sqllite DB to MySql DB

1. python manage.py dumpdata -o datadump.json
2. Change settings.py to your mysql

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'database',
        'USER': 'username',
        'PASSWORD': 'password',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
    }
}

3. Check you have mysqlclient, else run below command: 

       

       pip install mysqlclient

4. python manage.py migrate --run-syncdb5. python manage.py loaddata datadump.json

Setting up Python Virtual Environment

Follow below steps to create a virtual environment with python 2.7 version, 

you can replace it with any other version.

    $ pip install virtualenv
    $ virtualenv -p python2.7 mypython27

   Run below command to activate python 2.7 virtualenv

   > Go to mypython27 directory
   $ . bin/activate

How to Change Default Version of Python to another Version

Follow the below steps to change default version of python to another version:

Step 1: Go to /home/<user> directory in your terminal.
Step 2: Run sudo vim ~/.bashrc_aliases
Step 3: Add alias python=python3
Step 4: Run source ~/.bashrc_aliases

This will change the version from default to python3 version.

Passing Config File Variables in Other Python Files

Assume you have a config file (ETLConfig.cfg) with below texts (variables):

[ETL]
etldbname = *****
etllogin = ******
etlpassword = ****
etlport = ****
etlserver = *****

[FPATH]
filepath = D:/ETLFile/

Now we need to use these variable in other python files.

Create a python file (test.py) with below script:

1. Read variables from ETLConfig.cfg using configparser and write those variables to connection strings in your file:

import configparser
import sys
sys.path.append('C:\Python34\Lib\site-packages')
import pprint
import time
import datetime
import os
import csv
import sqlite3
from datetime import date, timedelta

import mysql.connector
from mysql.connector import errorcode

config = configparser.RawConfigParser()
config.read('ETLConfig.cfg')

#GET ETL CONFIG
m1servername = config.get('ETL', 'etlserver')
m1dbname = config.get('ETL', 'etldbname')
m1login = config.get('ETL', 'etllogin')
m1password = config.get('ETL', 'etlpassword')
m1port = config.get('ETL', 'etlport')

cnx = {
  'user': m1login,
  'password': m1password,
  'host': m1servername,
  'database': m1dbname,
  'raise_on_warnings': True,
  'use_pure': False
  }

melcnx=mysql.connector.connect(**cnx)
print(melcnx)

#GET ETL FILEPATH CONFIG
etlfilepath = config.get('FPATH', 'filepath')
print(etlfilepath )

2. On executing the python file (test.py), you can see the printed connection strings having same values available in CFG file.

How to Create Configuration File (*.cfg) Using Python?

The below code help you to create a sample config file using python script. The below code is tested with python 3.4 version:


Step 1:  Check you have python 3.* installed in your machine and also you have python configparser package installed.

Step 2: Create a python file with below scripts, say the python file is saved as CreateConfig.py.

import configparser

config = configparser.RawConfigParser()

#MYSQL_MEL
config.add_section('ETL')
config.set('ETL', 'etldbname', '*****')
config.set('ETL', 'etllogin', '******')
config.set('ETL', 'etlpassword', '****')
config.set('ETL', 'etlport', '****')
config.set('ETL', 'etlserver', '*****')

#FilePath
config.add_section('FPATH')
config.set('FPATH', 'filepath', 'D:/ETLFile/')


# Writing our configuration file to 'ETLConfig.cfg'
with open('ETLConfig.cfg', 'wt') as configfile:
    config.write(configfile)

Step 3: On executing CreateConfig.py file, you can see in your working directory, a new CFG file named ETLConfig.cfg is created with below texts.

[ETL]
etldbname = *****
etllogin = ******
etlpassword = ****
etlport = ****
etlserver = *****

[FPATH]
filepath = D:/ETLFile/

This ETLConfig file can be called in other python files, and the variables can be used as Global Variables in all packages.

Installing Numpy, Sci-py, Cython, and Pandas in Ubuntu machine

Numpy, Sci-py, Cython, and Pandas are the important pacjages used in python data analysis. In order to perform operations using these packages we need to install them in to the system. Execute the following scripts in terminal:

sudo apt-get install python-numpy
sudo apt-get install cython
sudo apt-get install python-scipy
sudo apt-get install python-pandas

Creating ODBC Connection in Python

Creating ODBC Connection in Python

STEP 1: Installing pypyodbc can be done via the commandline:

           >>C:\Python34\Scripts>pip install pypyodbc

Using the below command in your code for better naming conventions:

           >> import pypyodbc as pyodbc

Python Script to Connect to SQL Server and Retrieve Data From a Table

# Python Script to Connect to SQL Server and Retrieve Data From a Table

import sys
import pypyodbc

# Create a new database:
connection_str =    """
                    Driver={SQL Server Native Client 11.0};
                    Server=localhost;
                    Database=master;
                    Trusted_Connection=yes;
                    """
db_connection = pypyodbc.connect(connection_str)
db_connection.autocommit = True
db_cursor = db_connection.cursor()
sql_command =   """
                CREATE DATABASE MYDB
                """
try:
    db_cursor.execute(sql_command)
except pypyodbc.ProgrammingError:
    print("Database 'MYDB' already exists.")
db_connection.autocommit = False

db_cursor.close()
del db_cursor
db_connection.close()

# Connect a database.
connection_str =    """
                    Driver={SQL Server Native Client 11.0};
                    Server=Localhost;
                    Database=MYDB;
                    Trusted_Connection=yes;
                    """
db_connection = pypyodbc.connect(connection_str)
db_connection.autocommit = True
db_connection.autocommit = False
db_cursor = db_connection.cursor()

# Select rows from a table:
# 1) Select all columns of all rows:
sql_command =   """
                SELECT *  FROM [ods].[MYTABLE]
                """
db_cursor.execute(sql_command)
rows = db_cursor.fetchall()
for row in rows:
      userid, fname= row[0] ,row[1]
  
      # Now print fetched result
      print (userid,fname)