Specific sample code for working with SQLite3 in Python

SQLite3 can be said to be the easiest database to handle in Python because it is included in Python from the beginning and the DB can be saved as a file.

Therefore, here, make a note of the sample code for writing / reading SQLite3 data using Python.

Basic

import sqlite3

# Specifying the connection DB file
conn = sqlite3.connect('example.sqlite3')
conn.row_factory = sqlite3.Row 
c = conn.cursor()

# Execution of SQL statement
sql="create table persons(name, age, job)"
c.execute(sql)

# Save changes
conn.commit()

# Disconnect from DB
conn.close()

After executing the SQL statement, you need to execute "conn.commit ()" for the change to take effect.

Table operation

Creating a table

sql="create table persons(name, age, job)"
c.execute(sql)

Here, as an example, we are creating a "persons" table with three columns, name, age, and job.

Add column to table

sql="alter table persons add column address"
c.execute(sql)

Added "address" column to "persons" table

Delete table

sql="drop table persons"
c.execute(sql)

The process of deleting the "persons" table

Rename table

sql="alter table persons rename to workers"
c.execute(sql)

Renamed "persons" table to "workers"

Manipulating data

Add data (INSERT)

sql="insert into persons values ('Ann', '20','apprentice')"
c.execute(sql)

Data change (UPDATE)

sql="update persons set age=21 where name='Ann'"
c.execute(sql)

This code changes the Age of "name ='Ann'" in the "persons" table to 21.

DELETE data

sql="delete from persons where age<20"
c.execute(sql)

This code deletes data with age 20 or less in the "persons" table.

Data retrieval

Basic

sql="select * from persons"
c.execute(sql)

If you want to take out all, this code is enough. If the data size is small, it may be easier to understand if you get everything from the DB and then process it with Pandas on the Python side.

Expand the retrieved data to a list

import sqlite3

# Connect to DB
conn = sqlite3.connect('example.sqlite3')
 conn.row_factory = sqlite3.Row #Specify type
c = conn.cursor()

# Get the contents of the DB
c.execute('select * from persons')
results = c.fetchall()

# Create an empty list for expansion
persons_list=[]

# Processing to expand to list
for r in results:
    persons_list.append(r)

# Disconnect from DB
conn.close()

# display
display(persons_list)

Row data is stored in "persons_list". When taking out

persons_list[0]['name']
 ann

You can take it out in this way.

Partial extraction (column name specified)

sql="select name from persons"
c.execute(sql)

Code to retrieve only the name column

Partial retrieval (condition specification)

sql="select * from persons where age>20"
c.execute(sql)

Extract rows with age 21 and above

Specify the data retrieval order

# Take out in ascending order
sql="select * from persons order by age asc"
c.execute(sql)

# Take out in descending order
sql="select * from persons order by age desc"
c.execute(sql)

Reference page

[\ [Python3 ] \ SQLite3 ] Easy way to get records in dictionary type \ (dict type ) -Qiita Introduction to SQLite The first database from Python to SQLite3! -Qiita