Import Excel to MySQL using Python

I have always wondered how easy or difficult it is to import an excel document to MySQL using Python. Well after doing some research and code modifications I have finally managed to import an excel document to MySQL using Python’s xlrd library

Following are the steps I have followed in order to import the document
# Open the workbook and define the worksheet
# Establish a MySQL connection
# Get the cursor, which is used to traverse the database, line by line
# Create the INSERT INTO sql query
# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the header
# Assign values from each row
# Execute SQL Query
# Close the Cursor
# Commit the transaction
# Close the database connection
# Print results

Code:

1. #Create a Excel document with data that provides a summary of the information about the project managers, clients and the projects they have worked on along with the status of the project whether it is closed or opened. The beginning date of the project and project code

Column Headers – Job Code, Date, Client, Description, Status, Project Manager

Save the excel file as “.xls”

2. Create Columns in MySQL

3. Code to import the data into MySQL.

4. Use any code editor and save the file as importExcel.py

Code

import xlrd
import MySQLdb

# Open the workbook and define the worksheet
book = xlrd.open_workbook(“importExcel.xls”)

sheet = book.sheet_by_name(“Client_Data”)
//sheet = book.sheet_by_index(0)

# Establish a MySQL Connection
database = MySQLdb.connect (host=“localhost”, user=“root”, passwd=“”, db=“mysqlPython)

# Get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()

# Create the INSERT INTO sql query
query = “””INSERT INTO order (job_code, date, client, description, status, project_manager) VALUES (%s, %s, %s, %s, %s,%s)”””

# Create a for loop to iterate through each row in the xls file, starting from row 2
for r in range(1, sheet.nrows):
product = sheet.cell(r,0).value
customer = sheet.cell(r,1).value
rep = sheet.cell(r,2).value
date = sheet.cell(r,3).value
actual = sheet.cell(r,4).value
expected = sheet(r,5).value
open_opportunities = sheet(r,6).value

# Assign values from each row
values = (job_code, date, client, description, status, project_manager)

# Execute sql query
cursor.execute(query, values)
# Close the cursor
cursor.close()

#Commit the transaction
database.commit()
#Close the database connection
database.close()
#Print results
print “”
print “Data Imported successfully!!!”
print “”
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print “Summary of data imported: “ + columns + “ columns and “ + rows + ” rows”

Hope this helps in importing your spreadsheets🙂

Simple – Linux BIND / DNS Server Install using Webmin

BIND – Berkeley Internet Name Daemon (BIND) is the reference implementation of the Domain Name System (DNS) protocols. The software consists, most prominently, of the DNS Server component, called named. In addition the suite contains various administration tools, and a DNS resolver interface library. It is the most widely used DNS software.

It is a recommended practice that you have two nameservers. They will act as a primary and a secondary servers in case one fails.

The steps below outlines the installation and configuration of BIND using Webmin on a CentOS 6 server.

Installation and Configuration

Install BIND

  • #yum install bind bind-utils bind-libs bind-chroot caching-nameserver

Set the DNS service to start on boot

  • #chkconfig named on

Start DNS Service

  • #service named start

Setting up the bind server using webmin

  • Please refer to installing webmin at the following link – Installing Webmin
  • Modify the /etc/resolv.conf file
  •       domain  <domain_name_of_choice>
  •       search   <domain_name>
  •       nameserver <ip_address_of_the_chosen_server>
  • Disable Firewall if necessary
  • Change the hostname
  •        hostname <hostname>
  • Change the hosts file to reflect the above changes
  •        vi /etc/hosts
  •         <ip_address>      <hostname>
  •         <ip_address>      <domain_name>
  • Delete any other entry found in this hosts file
  • Check hostname by running the command
  •          #hostname

Access Webmin from any browser. Once you are logged in refresh all the modules so that Webmin can pickup its status.

Configure Webmin to use the bind / dns module

  • After installing the bind-chroot command
  • Click Module Config under – BIND DNS Server
  • Change the chroot directory to run BIND under – /var/named/chroot
  • Select Yes for – Is named.conf under chroot directory

Create bind services using the following

  • Server -> BIND DNS Server -> Create Master Zone
  • Fill the boxes and click create
  •     Domain name / network – <domain_name>
  •     Master Server – <domain_server>
  •     Email Address – Use any email address that you wish to use
  •     Select Yes to – Use zone template
  •     Click Create
  •     Go to – Address

Add Address Record Page

  • Enter the name of the server
  • Enter the IP Address of the server
  • Click Create

Add the reverse address of the server

  • Click reverse address
  • Type the IP Address
  • Type the hostname
  • Click Create

Once both the forward and reverse entries are created test the DNS Server

  • Run the hostname command – #hostname
  • Run the ping command – #ping <hostname>
  • Run the ping command – #ping <domain_name>
  • Run the dig command – #dig <domain_name>
  • Run the nlookup command – #nslookup <ip_address>

This is the end of – Simple – Linux BIND / DNS Server Install using Webmin

We can now add A and PTR records to the zone files.

 

Linux Networking – Interface Bonding / Teaming

Linux bonding driver provides a method to aggregate multiple network interface cards into a single logical / teamed / bonded interface. The behaviour depends on the mode of the bonding that we choose, but generally speaking the modes provide either hot standby or load balancing.

Bond Interfaces can be very useful if you want to divide the entire network using VLANs and allow per VLAN traffic with the view to have reliable and redundant network connectivity. Here, I am trying to provide a procedure to create a bonded interface under CentOS 6.4. This is not a new concept but it provides a tested procedure that I have used to create my setup which is named as “The Matrix” – The Matrix

CREATING A BONDED INTERFACE

  • In order for us to have a reliable network and have the ability to use VLAN’s a bonded interface is created so that the load can be split on both the interfaces with the flexibility of using more than one network
  • To separate the traffic the use VLANs and bridge interfaces is common. For the separation, we used VLANs and the bridge interfaces that would be connecting to the virtual hosts would not be connected to the physical interfaces, but instead would be connected to the virtual interfaces that we would be configuring as part of the Linux VLAN configuration
  • Based on the article at the following location, I decided to put together the following configuration which will be used for the Controller and the Compute Nodes
  • Setting up the /etc/sysconfig/network configuration file
    • #vi  /etc/sysconfig/network
    • Add / Change the following information
      • HOSTNAME=<hostname_of_the_controller>
      • NETWORKING=yes
      • #IPV4
      • NOZEROCONFIG=yes
      • #IPV6
      • NETWORKING_IPV6=yes
      • IPV6INIT=yes
  • Go to /etc/sysconfig/network-scripts
  • Create interface – bond0
      • DEVICE=bond0
      • IPV6INIT=no
      • NM_CONTROLLED=no
      • USERCTL=no
      • BOOTPROTO=none
      • ONBOOT=yes
      • NOZEROCONF=yes
      • TYPE=unknown
  • Change interface – eth0
      • DEVICE=eth0
      • HWADDR=00:25:90:1A:E9:76
      • TYPE=Ethernet
      • UUID=f48b72fb-ae8f-4202-b707-23c88b79ef31
      • ONBOOT=yes
      • NM_CONTROLLED=yes
      • BOOTPROTO=none
      • MASTER=bond0
      • SLAVE=yes
      • NOZEROCONF=yes
  • Change Interface – eth1
      • DEVICE=eth1
      • HWADDR=00:25:90:1A:E9:77
      • TYPE=Ethernet
      • UUID=13c7c9ec-0bdc-432f-a898-f4e5cd3c0358
      • ONBOOT=yes
      • BOOTPROTO=none
      • USERCTL=no
      • MASTER=bond0
      • SLAVE=yes
      • NOZEROCONF=yes
  • Modify the file – /etc/modprobe.d/bonding.conf
      • alias bond0 bonding
      • options bond0 mode=5 miimon=100 arp_interval=100
      • #modprobe  bonding
  • Since I was using VLANs, I will now create the VLAN interface on top of bond0, bond0.140 and bond0.180 pointing to the appropriate bridge interfaces
    • #vi  /etc/sysconfig/network-scripts/ifcfg-bond0.140
      • DEVICE=bond0.140
      • VLAN=yes
      • BOOTPROTO=static
      • ONBOOT=yes
      • TYPE=Ethernet
      • IPADDR=<ipaddress>
      • NETMASK=255.255.255.0
      • GATEWAY=<gateway_ipaddress>
  • #vi  /etc/sysconfig/network-scripts/ifcfg-bond0.180
      • DEVICE=bond0.180
      • VLAN=yes
      • BOOTPROTO=static
      • ONBOOT=yes
      • TYPE=Ethernet
      • IPADDR=<ipaddress>
      • NETMASK=255.255.255.0
      • GATEWAY=<gateway_ipaddress>
  • Once the networking was configured, applied static routes to the interfaces did not work and I was unable to ping both the bonded interfaces. The interface that I was able to ping was the one that came up first. Following was the output of the routing table
    • router_output
  • After doing some research, I concluded that I can use the Linux kernel as routers and as many routers do they can route packets from one network to the other.
  • In my network I am using two VLAN <vlan_id> and <vlan_id>, so the following changes were made in order to ping both the networks
      • #cd /etc/
      • #vi sysctl.conf
      • Look for – net.ipv4.conf.default.rp_filter
      • Change the value from 1 to 2. And if this value is not available then add the value.
      • Reload sysctl
      • #sysctl  -p
  • Finally I got this working and all the tests for redundancy is finally completed.

INSTALLING APACHE ON CENTOS 6.4 – PART 2

IS THE SERVICE LISTENING?

  • Run the command lsof –i to see open ports; look for processes listening to port 80 (http)
    • #lsof  –i | grep http
  • Browse to http://localhost to see the default page
  • Examine the error log for clue
    • #/var/log/httpd/error_log
  • Verify no other process is listening on port 80
  • If SELinux is enabled, try disabling it

TESTING THE INSTALLATION

INSTALLATION FAILURES

  • Examine the error log for clue
    • #/var/log/httpd/error_log
  • Verify no other process is listening on port 80
  • If SELinux is enabled, try disabling it

INSTALLING APACHE ON CENTOS 6.4 – PART 1

 

PROCESS TO INSTALL APACHE

  • Install Apache on a standard linux installation
  • Serve a single site
  • Serve multiple sites with virtual hosts
  • Perform user authentication and access control
  • Setup a secure site (https)
  • Serve multiple sites with virtual hosts
  • Control logging and status reporting
  • Apache is in the CentOS repositories and is easy to install
    • #yum install httpd
  • The # prompt indicates that you need to do this as root
  • A number of dependencies will also be installed
    • #service httpd start
    • #chkconfig httpd on
  • This command ensures that the service will be started at boot time
  • This command starts the service immediately

INSTALLING APACHE

  • Apache is in the CentOS repositories and is easy to install
    • #yum install httpd
  • The # prompt indicates that you need to do this as root
  • A number of dependencies will also be installed

STARTING THE SERVICE

    • #service httpd start
    • #chkconfig httpd on
  • This command ensures that the service will be started at boot time
  • This command starts the service immediately

Installing ZFS on CentOS 6.4

Introduction

  • UPDATE THE SYSTEM
    • Install the updates for the CentOS by running the following command
      • $yum –y update
  • Restart the server after installing all the updates
  • INSTALLING ZFS ON CENTOS 6.4
  • Using ZFS on Linux is an attractive solution for a high-performance NFS server due to several key factors
    • Cost, ability to use commodity hardware with free software
    • Simplicity, quick install and easy to configure / manage
    • Flexibility, ZFS offers a plethora of options for your filesystems needs
    • After upgrading the system in the previous step do the following
      • $yum localinstall –nogpgcheck  http://archive.zfsonlinux.org/epel/zfs-release-1-3.e16.noarch.rpm
      • $yum install zfs
      • The next step is to install the ZFS module (drivers) with the following command
        • $modprobe zfs
        • I did not have to use this command on a CentOS 6.4, so after installing the zfs in the previous step, I restarted the server
        • The update command and the zfs installation installed all the new updates with the new kernel version.
        • Now that zfs has been installed, let us make sure it is loaded appropriately by using the following command
          • $lsmod | grep zfs
          • zfs
          • Now I want to create my ZFS array, to do that I need to find the device ID’s of my hard drives. Running the command
            • $fdisk -l | grep 2000.4
            • Gives me a list of the 2TB drives that I will be using for the zfs array

Setting up Webmin on CentOS 6.4

SETUP WEBMIN
INTRODUCTION

  • This will show you how to setup Webmin using yum repository in CentOS 6.4.
  • Webmin is an open source system administration and system configuration tool which provide you web-based interface to manage, administer and configure your server.
  • It provides graphical interface remote administration instead of manually editing the configuration using command line from putty or console.

INSTALLATION PROCESS

  • Enabling yum repository for Webmin
  • Create the /etc/yum.repos.d/webmin.repo file with the following contents
  •                        $vi /etc/yum.repos.d/webmin.repo
  • Add the following lines to the file

[Webmin]
name=Webmin Distribution Neutral
#baseurl=http://download.webmin.com/download/yum
mirrorlist=http://download.webmin.com/download/yum/mirrorlist
enabled=1

  • Fetch and install the GPG Key
  •                  $wget http://www.webmin.com/jcameron-key.asc
  •                  $rpm –import jcameron-key.asc
  • Run “yum install” command to install all required dependencies
  •                  $yum install webmin –y
  • By default Webmin runs on port 10000, therefore port 10000 need to be open in order to allow you to access web base interface remotely
  •                  $sudo iptables –I INPUT –m state –state NEW -m tcp –p tcp –dport 10000 –j ACCEPT
  •                  $sudo service iptables save
  •                  $sudo service iptables restart
  • Access and login to Webmin using URL – http://ip_address_of_server:10000