Logo F2FInterview

Python Advanced Interview Questions

Q   |   QA

Python likes to be able to keep track of where it last left off in reading and writing to the database. In psycopg, this is called the cursor, but we will use the variable 'mark' for our program. So, we can then construct the following assignment:

mark = connection.cursor()

While some SQL insertion formats allow for understood or unstated column structure, we will be using the following template for our insert statements:

INSERT INTO <table> (columns) VALUES (values) ;

While we could pass a statement in this format to the psycopg method 'execute' and so insert data into the database, this quickly becomes convoluted and confusing. A better way is to compartmentalize the statement separately from the 'execute' command as follows:

statement = 'INSERT INTO ' + table + ' (' + columns + ') VALUES (' + values + ')'
mark.execute(statement)

In this way, form is kept separate from function. Such separation often helps in debugging. 

After passing the data to PostgreSQL, we must commit the data to the database:

connection.commit()

Now we have constructed the basic parts of our function 'insert'. Put together, the parts look like this:

connection = psycopg.connect('dbname=Birds', 'user=robert')
mark = connection.cursor()
statement = 'INSERT INTO ' + table + ' (' + columns + ') VALUES (' + values + ')'
mark.execute(statement)
connection.commit()

You will notice that we have three variables in our statement: table, columns, and values. These thus become the parameters with which the function is called:

def insert(table, columns, values):

We should, of course, follow that with a doc string:

'''Function to insert the form data 'values' into table 'table'
according to the columns in 'column' '''

We have a function for inserting data into a table of our choice, using columns and values defined as needed.

def insert(table, columns, values):
      '''Function to insert the form data 'values' into table 'table' according to the columns in 'column' '''

      connection = psycopg.connect('dbname=Birds', 'user=robert')
      mark = connection.cursor()
      statement = 'INSERT INTO ' + table + ' (' + columns + ') VALUES (' + values + ')'
      mark.execute(statement)
      connection.commit()
      return

To call this function, we simply need to define the table, columns, and values and pass them as follows:

type = "Owls"
fields = "id, kind, date"
values = "17965, Barn owl, 2006-07-16"

insert(type, fields, values)

In order to link this F2FInterview's page as Reference on your website or Blog, click on below text area and pres (CTRL-C) to copy the code in clipboard or right click then copy the following lines after that paste into your website or Blog.

Get Reference Link To This Page: (copy below code by (CTRL-C) and paste into your website or Blog)
HTML Rendering of above code: