Python sqlite insert list

Python sqlite3 – INSERT Multiple Rows to TABLE

In our previous article – insert row into sqlite3 table, we have seen how to insert a single record to a table. In this tutorial, we will learn how to insert multiple rows using a single INSERT INTO query.

Steps to insert multiple rows into sqlite3 table

The steps to insert multiple records to a table are:

1. Prepare the connection to the database and then get a cursor.
2. Get all your records to a list.
3. Use executemany() method and pass the query and records list as arguments to the method.

Examples

1. Insert multiple rows into sqlite table

In the following example, we will insert three records into a table.

Python Program

import sqlite3 conn = sqlite3.connect('mysqlite.db') c = conn.cursor() #records or rows in a list records = [(1, 'Glen', 8), (2, 'Elliot', 9), (3, 'Bob', 7)] #insert multiple records in a single query c.executemany('INSERT INTO students VALUES(. );',records); print('We have inserted', c.rowcount, 'records to the table.') #commit the changes to db conn.commit() #close the connection conn.close()

To check if the query is executed successfully and inserted all the three records, we have printed cursor.rowcount property.

We have inserted 3 records to the table.

Summary

In this tutorial of Python Examples, we learned to insert multiple records into sqlite3 table in a single query with the help of well detailed examples.

Источник

Python: Insert a list of records into a given SQLite table

Write a Python program to insert a list of records into a given SQLite table.

Sample Solution:

Python Code :

import sqlite3 from sqlite3 import Error def sql_connection(): try: conn = sqlite3.connect('mydatabase.db') return conn except Error: print(Error) def sql_table(conn, rows): cursorObj = conn.cursor() # Create the table cursorObj.execute("CREATE TABLE salesman(salesman_id n(5), name char(30), city char(35), commission decimal(7,2));") sqlite_insert_query = """INSERT INTO salesman (salesman_id, name, city, commission) VALUES (?, ?, ?, ?);""" cursorObj.executemany(sqlite_insert_query, rows) conn.commit() print("Number of records after inserting rows:") cursor = cursorObj.execute('select * from salesman;') print(len(cursor.fetchall())) # Insert records rows = [(5001,'James Hoog', 'New York', 0.15), (5002,'Nail Knite', 'Paris', 0.25), (5003,'Pit Alex', 'London', 0.15), (5004,'Mc Lyon', 'Paris', 0.35), (5005,'Paul Adam', 'Rome', 0.45)] sqllite_conn = sql_connection() sql_table(sqllite_conn, rows) if (sqllite_conn): sqllite_conn.close() print("\nThe SQLite connection is closed.") 

Number of records after inserting rows: 5 The SQLite connection is closed.

Python Code :

import sqlite3 conn = sqlite3 . connect ( 'mydatabase.db' ) cursor = conn.cursor () #create the salesman table cursor.execute("CREATE TABLE salesman(salesman_id n(5), name char(30), city char(35), commission decimal(7,2));") # Insert records rows = [(5001,'James Hoog', 'New York', 0.15), (5002,'Nail Knite', 'Paris', 0.25), (5003,'Pit Alex', 'London', 0.15), (5004,'Mc Lyon', 'Paris', 0.35), (5005,'Paul Adam', 'Rome', 0.45)] cursor . executemany ( """ INSERT INTO salesman (salesman_id, name, city, commission) VALUES (. ) """ , rows ) print ( 'Data entered successfully.' ) conn.commit () if (conn): conn.close() print("\nThe SQLite connection is closed.") 

Data entered successfully. The SQLite connection is closed.

Python Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource’s quiz.

Follow us on Facebook and Twitter for latest update.

Python: Tips of the Day

Why is �1000000000000000 in range(1000000000000001)� so fast in Python 3?

The Python 3 range() object doesn’t produce numbers immediately; it is a smart sequence object that produces numbers on demand. All it contains is your start, stop and step values, then as you iterate over the object the next integer is calculated each iteration.

The object also implements the object.__contains__ hook, and calculates if your number is part of its range. Calculating is a (near) constant time operation *. There is never a need to scan through all possible integers in the range.

From the range() object documentation:

The advantage of the range type over a regular list or tuple is that a range object will always take the same (small) amount of memory, no matter the size of the range it represents (as it only stores the start, stop and step values, calculating individual items and subranges as needed).

So at a minimum, your range() object would do:

class my_range(object): def __init__(self, start, stop=None, step=1): if stop is None: start, stop = 0, start self.start, self.stop, self.step = start, stop, step if step < 0: lo, hi, step = stop, start, -step else: lo, hi = start, stop self.length = 0 if lo >hi else ((hi - lo - 1) // step) + 1 def __iter__(self): current = self.start if self.step < 0: while current >self.stop: yield current current += self.step else: while current < self.stop: yield current current += self.step def __len__(self): return self.length def __getitem__(self, i): if i < 0: i += self.length if 0 '.format(i)) def __contains__(self, num): if self.step < 0: if not (self.stop < num 


This is still missing several things that a real range() supports (such as the .index() or .count() methods, hashing, equality testing, or slicing), but should give you an idea.
I also simplified the __contains__ implementation to only focus on integer tests; if you give a real range() object a non-integer value (including subclasses of int), a slow scan is initiated to see if there is a match, just as if you use a containment test against a list of all the contained values. This was done to continue to support other numeric types that just happen to support equality testing with integers but are not expected to support integer arithmetic as well. See the original Python issue that implemented the containment test.
* Near constant time because Python integers are unbounded and so math operations also grow in time as N grows, making this a O(log N) operation. Since it's all executed in optimised C code and Python stores integer values in 30-bit chunks, you'd run out of memory before you saw any performance impact due to the size of the integers involved here.

Weekly Trends
Java Basic Programming Exercises
SQL Subqueries
Adventureworks Database Exercises
C# Sharp Basic Exercises
SQL COUNT() with distinct
JavaScript String Exercises
JavaScript HTML Form Validation
Java Collection Exercises
SQL COUNT() function
SQL Inner Join
JavaScript functions Exercises
Python Tutorial
Python Array Exercises
SQL Cross Join
C# Sharp Array Exercises

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook
Источник