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.

Database Programming Python Tutorial, Creating a Table and Inserting Values in It with Python

In python programming the module named sqlite3 or _MySQL can be used to database operations(Along with many others). The basic principal of writing database program is same for more or less every database in python. You have to create a way to communicate with the database and then execute the SQL queries through python. Lets see it by an example - 

import sqlite3 as db
conn = db.connect('test1.db')
cur=conn.cursor()
cur.execute('drop table if exists Employee-info')
cur.execute("CREATE TABLE Employee-info (uniqueid integer primary key AUTOINCREMENT,name TEXT,dept TEXT, address TEXT,profession TEXT,status TEXT DEFAULT 'Single');")
conn.commit()

The above code imported the sqlite3 module and renamed it as db.We have made a connection to the database with the name test1.db, if it is not present there the said line will create one db in the current working directory. (If you don't know how to find current working directory then see here.)

Now , the next line created a cursor for the connected database. You can say the cursor is the way of operating different commands to the database in SQL mode. In the next line you can see the cur.execute() statement executes a SQL command to the database. It simply find a previous table named Employee_Info and deletes it, so that we can create a table with the same name.

In the next line we created table with required value, Again I have used SQL command within the cur.execute() to create the table. The table is created with primary key uniqueid which will be automatically incremented every time when a new entry to the database will occur.

Now as we have a basic understanding of creating the database and a table, lets try to insert some value in it. Do you know what is the basic command in SQL for inserting values into database ?
 It is - 
INSERT INTO Employee_info  VALUES ('name','dept','address',...);  
In python we can do the same by using the cur.execute again but with certain modifications. For this example lets do it the following way, there are more way which I will discuss later - 
rowdata=('Dhruba','IT','DNK','IT','S')
cur.execute('insert into troublebook(name,dept,address,profession,status)\ values(?,?,?,?,?,?)',rowdata)  
conn.commit()
This will insert values in the database. The rowdata contains all the needed values, so the program will run without a problem. We can executemany by implying cur.executmany() with rowdata holding more rows of values .

Please visit http://pythonbeginner.in for more topics.