SQLite3 Python

Introduction

SQLite is a compact embedded database with open source.

• Does not use the client-server paradigm
• Stores the entire database (including definitions, tables, indexes, and data) in a single standard file on the computer running the program
• Before the start of execution of the transaction, the entire file that stores the database is blocked.

In this article, you will learn how to work with SQLite3 in Python 3.

Import from .csv file

Let's say you have a file. data.csv The values are separated by a semicolon.

Import the contents of this file into the SQLite3 database.

You can find in this article several ways to make such an import.

The simplest example.

Create a file. from_csv.py And copy the code from the examples.

You can start the file command python3 from_csv.py

import csv, sqlite3 con = sqlite3.connect('db/my.db') cur = con.cursor() cur.execute("CREATE TABLE t (picture, price, number);") # picture, price, number are column names with open('data.csv','r') as fin: # by default csv.DictReader uses the first row as column headers dr = csv.DictReader(fin, delimiter=";") to_db = [(i['picture'], i['price'], i['number']) for i in dr] cur.executemany("INSERT INTO t (picture, price, number) VALUES (?, ?, ?);", to_db) con.commit() con.close()

python3 from_csv.py

A file should appear in the db directory my.db

This database should have a table. t repeating the content of your .csv file

A more realistic example

Now let’s look at a more realistic example.

The table should have an id column. It is advisable to generate it automatically.

It is also easier to read the code with the help of docstring

Let's look at the file. partners.csv listing

name;url;area URN.SU;https://urn.su;IT HeiHei.ru;https://heihei.ru;Travel TopBicycle.ru;https://topbicycle.ru;Bicycles Aviasales;https://aviasales.ru;Travel Booking.com;https://booking.com;Hotels Hotellook;https://Hotellook.com;Hotels Velodrive;https://velodrive.ru;Bicycles Xiaomi;https://mi-shop.com;Android Samsung;https://www.samsungstore.ru;Android ;https://.ru;Books GeekBrains;https://gb.ru;Education Netology;https://netology.ru;Education TestSetup.ru;https://TestSetup.ru.ru;Education Pluralsight;https://Pluralsight.com;Education SovcomInsurance;https://sovcomins.ru;Insurance Polis 812;https://polis812.ru;Insurance Vivo;https://ru.vivo.com/;Android Beget;https://beget.com;Hosting Reg.ru;https://Reg.ru.ru;Hosting OLDI;https://oldi.ru;Laptops

The file has three values per line: name, url, area. Add these columns to the database.

In the names there are Cyrillic symbols, so when opening, specify encoding="utf8"

Also, do not forget to generate id.

A new, more real-life file friends.py It will look like this:

import csv, sqlite3 con = sqlite3.connect('db/partners.db') cur = con.cursor() cur.execute("""CREATE TABLE friends ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, url TEXT, area TEXT )""") with open('partners.csv','r', encoding="utf8") as f: dr = csv.DictReader(f, delimiter=";") to_db = [(i['name'], i['url'], i['area']) for i in dr] cur.executemany("INSERT INTO friends (name, url, area) VALUES (?, ?, ?);", to_db) con.commit() con.close()

python3 friends.py