Python Developer- insert data records to a database (SQL)

Here is an example for inserting data to SQLite database.
You can see how to create a table, check if a table exists, and the use of single and multiple record insertion.


import sqlite3
import os



#-----------------------------------------------------------------------
#you need the path for the database
script_directory=os.getcwd()+os.path.sep

print script_directory

#this is the db we're creating

file_name="pydb.sqlite"
table_name='xx'
#Connect to the database
# Note - if an SQLite file desn't exists, the connect method will create it. If it exists, it'll open it.
con=sqlite3.connect(file_name)
#Open a cursor-this will move through a record set and read (or write)
cur=con.cursor()

#-----------------------------------------------------------------------

#Create a table, if not exist

sql=""" SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND tbl_name=? """
cur.execute(sql,[table_name])
table_exits=cur.fetchone()[0]
print table_exits
if table_exits==0:
	print 'create the %s table '% table_name
	# This code can run with any DB, not only SQLite
	sql=""" 
		CREATE TABLE %s
			(
				id INT,
				name VARCHAR(200)
			
			)
		""" % table_name
	#run the creation with the connect object.
	con.execute(sql)

#If you need a GUI for SQLite, one option is to use the FireFox add-on, works great.

#-----------------------------------------------------------------------
#-----------------------------------------------------------------------

#Insert statement for inserting new values
#it's alway good practice to spell the field names


sql="""INSERT INTO %s (id,name)""" % table_name


#The VALUES are 2 parameters that will follow later when executing
# the ? is a parameter

# Note - this may NOT work the same with different databases.

sql+=""" VALUES (?,?) """

#Here are 2 ways to add a record:
#-----------------------------------------------------------------------
#1) record by record 

#the parameters are a list

#inserting 2 records one by one
for i in range(1,3):
	
	params=[i,'Person '+str(i)] #'Person '+str(i) will make the names unique
	#execute via cursor 
	cur.execute(sql,params)
	# to save the new record, use commit method of the connection.
	con.commit()
	print 'run no. ',i
#-----------------------------------------------------------------------
#2) insert multiple (2) records - faster and efficient

#the parameters are lists in a list

params=[] # Note - you assign/declare the main list here
for i in range(3,5):
	params.append([i,'Person '+str(i)]) #append a list for each record
	
#execute via cursor 
cur.executemany(sql,params) # run all at once
# use commit method of the connection.
con.commit()
print '1 run  for all'
#-----------------------------------------------------------------------