Adding Data to SQLite with Python

 in Python  tagged insert / add / python / connection / sqlite / cursor / adding by

Do you need to add data to your SQLite database inside your Python project?

Do you have a DataFrame in your Python project, or do you want to add the data manually? Check out this post here called Pandas DataFrame to SQLite.

Would you like to use an INSERT INTO statement in Python to put your data into a SQLite database? Let’s create a project to do this. My Python project is called Python Insert Into SQLite. We are going to ask the user at run time. I will respond with Jane Foster and Researcher.

view source
print?
1# what if we got the data from the user?
2 
3name = input("Name? ")
4position = input("Position? ")
5 
6connection = sqlite3.connect('D:\Test\emp.db')
7cursor = connection.cursor()
8cursor.execute('''CREATE TABLE IF NOT EXISTS Employees
9               (Emp_Id INT, Name TEXT, Position TEXT)''')
10cursor.execute('''INSERT INTO Employees (Emp_Id, Name, Position) VALUES(?,?,?)''', (5, name, position))
11connection.commit()
12connection.close()

Name? Jane Foster
Position? Researcher

Using the syntax with the question marks help prevent SQL injection attacks.

What if we wanted to enter in several at the same time? We still are doing this manually in Python code.

view source
print?
1# what if we want to enter many at a time
2 
3connection = sqlite3.connect('D:\Test\emp.db')
4cursor = connection.cursor()
5cursor.execute('''CREATE TABLE IF NOT EXISTS Employees
6               (Emp_Id INT, Name TEXT, Position TEXT)''')
7# let's create a list of tuples...
8# square brackkets create lists and paremtheses create tuples
9employee_list = [(6, 'Herbert', 'Boot Salesman'),
10                 (7, 'Rosemary', 'Architect')]
11cursor.executemany('''INSERT INTO Employees (Emp_Id, Name, Position) VALUES(?,?,?)''', employee_list)
12connection.commit()
13connection.close()

We use executemany instead of execute.

Leave a Reply