Python – Insert Data – MS SQL

Overview

Building on top of the VirtualInvestor database from the Python Connect to MS SQL tutorial.

This is a quick example of how to insert records to a MS SQL database table.

Instructions

Input the values from the command line as follows:

company = input("Company: ") 
ticker = input("Ticker: ") 
indexVal = input("Index: "

The SQL portion of this app will look like the following. Using question marks as the placeholder for the values.

SQLCommand = ("INSERT INTO Companies(Company, TickerSymbol, ParentIndex) VALUES (?,?,?)")  Values = [company, ticker, indexVal]

After the SQL Statement put in a commit.

connection.commit()

Here is the all the code needed to insert and view the results.

import pyodbc  
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"                       "Server=DESKTOP-JLUMQNO;" "Database=VirtualInvestor;" "Trusted_Connection=yes;")              

company = input("Company: ") 
ticker = input("Ticker: ") 
indexVal = input("Index: ") 
cursor = cnxn.cursor() 
SQLCommand = ("INSERT INTO Companies(Company, TickerSymbol, ParentIndex) VALUES (?,?,?)")  Values = [company, ticker, indexVal] cursor.execute(SQLCommand, Values) 
cnxn.commit() 

print("Data Successfully Inserted")    
cursor = cnxn.cursor() 
cursor.execute('SELECT * FROM Companies') 
for row in cursor:     
    print('row = %r' % (row,)) 
cnxn.close()

As a good habit, don’t forget to close your database connections.

connection.close()

You should see something similar to the following.