Introduction to Python : python database

Tuesday, November 10, 2015

A Program to Read Your xls File and Then Create a Database Through Python

I had a problem last week, I needed to convert an xls file and a create a database file form it , so that I can use it in flask architecture and create a web based application to store data. The old data of xls was important, so I can not start afresh without including the data. Below is the way I did the program to solve the problem -

1. First open the xls sheet and then I found and replace all the commas(,) with space , so that when I try to split the lines by comma the commas which are in each cell will be exempted. The next thing is to save the file as .csv(comma separated value) format.
2. Now all we need to do is to write the program which will spit the files couple of times and insert it in the database.

import sqlite3 as db
dbase=db.connect('hostname3')
cur=dbase.cursor()
##cur.execute('drop table if exists hostname')#
##cur.execute("CREATE TABLE hostname(serial_no text,workgroup TEXT,computer_name TEXT,dept TEXT, designation TEXT,location TEXT,ipaddress TEXT,io_box TEXT,system_model TEXT,ms_office TEXT,winxp_cdkey TEXT,os TEXT, ram TEXT, processor TEXT, monitor TEXT,keyboard TEXT, mouse TEXT,printer TEXT,cartridge TEXT,scanner TEXT,ups TEXT,spike_bus TEXT,network_printer_address TEXT);")

fh=open('new.csv','r+')

for line in fh:
    line=line.split('\n')
    for data in line :
data=(data[0],data[1],data[2],data[3],data[4],data[5]\
         ,data[6],data[7],data[8],data[9],data[10],data[11],\
         data[12],data[13],data[14],data[15],data[16],data[17],data[18]\
         ,data[19],data[20],data[21],data[22])
        print (data)

        cur.executemany("insert into hostname values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",(data,))

fh.close()
dbase.commit()
dbase.close()

I have commented the lines which will be needed if we run the program for the first time. The above program first creates a database and then create a table according to the original excel sheet. Then we open the newly created csv file , then for iterate through the file and split them when we find a newline. We again iterate through each line and split them when we find a comma. Then we execute the execute many command which will insert the whole data to the table. Don't run the program with python 2.7 ,instead use python 3 to run the program, otherwise it will give a some UTF encoding error. Python 2.7 is okay with single data insertion but when it comes to the executemany command it showed the error. If you find any solution to run the program in python 2.7 then please comment below. You can also visit my site http://www.pythonbeginner.in for more information.
It saved my time a lot. Hope it will help you too.

Sunday, August 16, 2015

A Combination of Both Python CGI and Database Programming

Python CGI can be helpful to get data from a form and insert it into a database. So, a combination of both the CGI programming and a little bit of database programming can help us to create and maintain a small web based database. See the example below -

Lets start with creating a form. In the form action you can see that I have called a python cgi file and use th method post. Always store the CGI file in the cgi-bin folder. Then we have created a text entry for username label and another for password level. We use the submit button to call the cgi file.
It will create a form like below -
<html>
<link rel="stylesheet" type="text/css" href="new1.css">
<head>
<title>Login Page</title>
<head>
<h1> Welcome to The Gate Pass Entry System</h1>
<h2>Please enter username and password to access the system</h2>
<body>
<form action='/cgi-bin/formvalidator.py' method='POST'>
<label>Username: </label>
<input type='text' name='username'><br><br/>
<label>Password: </label>
<input type="Password" name='password' ><br/><br/>
<input type='submit' value=submit>
</form>
</body>
</html>
The Output will be something like below -

The next thing we have to do is to handle the input from the form and get data from it ;then we will insert the data to a database. Lets do it -

#!/Python27/python (#!/usr/bin/python - in case of linux)
import cgi,cgitb
cgitb.enable() #This will show errors in the browser
data=cgi.FieldStorage() #The Fieldstorage function allow us to get data from form.
username=str(data['username'].value) #getting the value of the name='username' field
password=str(data['password'].value) #getting the value of the name='password' field

Now the data collection part is covered up, as we can see we have two variables username and password which are holding the inputs from the HTML form. Now lets put them in database. Please visit http://pythonbeginner.in/databaseprogramming if you have not done a bit of database programming with python.We will crate a table named login_list and put the data there.

import sqlite3 as db #You should import this in the first or second line of the program.
conn=db.connect('test1.db') #Connecting to database named test1.db
cur=conn.cursor()
cur.execute('drop table if exists login_list')
cur.execute("CREATE TABLE login_list (uniqueid integer primary key AUTOINCREMENT,usernameTEXT,password TEXT);")
conn.commit()
cur.execute('insert into troublebook(username,password) values(?,?)',(username,password))
conn.commit()
So, what the program actually did? It took the variables username and password and inserted into the login_list table for storing. It is basically a table for entering username and password but not verifying it. What if we need to verify if it is in the database and if not found then insert the value in the list.
Hint: The 'select * from table' is used to have the entire table as output. Can we use it here ? Obviously. but how, let's find out and stay tuned for the next blog entry.

Now lets traverse the list with the help of database and generate a HTML page on your browser showing all username and password.

import sys,cgi,cgitb
import sqlite3 as db
conn=db.connect('test1.db')
query='select * from login_list'
cur.execute(query)
rows=cur.fetchall()
sys.stdout.write("Content-type: text/html\r\n\r\n")
sys.stdout.write("")
sys.stdout.write("<html><body><h1>Login List :</h1><p>")
for row in rows:
   print ("_________________________________________________________<br/>\n")
   print ("\n")

   sys.stdout.write("ID No : %s <br>Name :%s <br>Password : %s" % (row[0], row[1], row[2]))
   sys.stdout.write("<br/>")

sys.stdout.write("</p></body></html>")

This will list the whole login_list database and place it in your web browser with a suitable format.You can use another program or another link to run this, the main thing i the concept of the CGI must be cleared now, as we will be moving to more complex structure of the cgi programming.

Introduction to Python

Tuesday, November 10, 2015

A Program to Read Your xls File and Then Create a Database Through Python

Sunday, August 16, 2015

A Combination of Both Python CGI and Database Programming

Feautured Post

Python Trivia Post: enumerate()