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

Recommended Posts

Specific sample code for working with SQLite3 in Python
[Introduction for beginners] Working with MySQL in Python
Working with LibreOffice in Python
Working with sounds in Python
[Python] Sample code for Python grammar
VS Code settings for developing in Python with completion
Working with LibreOffice in Python: import
Working with DICOM images in Python
Try working with binary data in Python
Tips for dealing with binaries in Python
Post Test 3 (Working with PosgreSQL in Python)
Process multiple lists with for in Python
Sample for handling eml files in Python
Sqlite in python
Read a Python # .txt file for a super beginner in Python with a working .py
Try working with Mongo in Python on Mac
Use Python in Anaconda environment with VS Code
Settings for Python coding in Visual Studio Code
Settings for getting started with MongoDB in python
Allow real-time code checking in Python development with VS Code
Use Cursur that closes automatically with sqlite3 in Python
Revive symbol search in Python workspace with VS Code
Tips for speeding up python code correctly with numba
Expose settings.json for efficient Python coding in VS Code
Python code for k-means method in super simple case
Sample code to get started with GLSL shaders in Processing (either Java or Python)
Get country code with python
Sample data created with python
Python with VS Code (Windows 10)
Scraping with selenium in Python
Scraping with chromedriver in python
Debugging with pdb in Python
Python: Working with Firefox with selenium
Search for strings in Python
Python code memo for yourself
Scraping with Selenium in Python
Techniques for sorting in Python
Debug Python with VS Code
Scraping with Tor in Python
Tweet with image in Python
Combined with permutations in Python
Generate QR code in Python
[Python] [SQLite3] Operate SQLite with Python (Basic)
Character code learned in Python
About "for _ in range ():" in python
Document Python code with Doxygen
Insert Import statements needed for Python code completion in Neovim
Tips for developing apps with Azure Cosmos DB in Python
Create a child account for connect with Stripe in Python
[For beginners] Summary of standard input in Python (with explanation)
Installation procedure for Python and Ansible with a specific version
(For myself) Flask_8 (Add / Edit / Delete in database with python)
Run BNO055 python sample code with I2C (Raspberry Pi 3B)
A sample for drawing points with PIL (Python Imaging Library).
I just wrote the original material for the python sample code
The story that Python stopped working with VS Code (Windows 10)
Sample code summary when working with Google Spreadsheets from Google Colab
Send push notifications to iOS apps with Python2 (with sample code)
[Python] Create a screen for HTTP status code 403/404/500 with Django
Can be used with AtCoder! A collection of techniques for drawing short code in Python!
Number recognition in images with Python