Easily handle databases with Python (SQLite3)

1. Background

I personally invest in my side business, but I want to do analysis automatically on my home PC. First of all, I learned the box (database) for storing data, so I will keep only the basic part as a record.

2. Database creation-operation

I thought it would be easier to understand the database in Excel for the time being, so this time I will replace the first part with Excel.

① First, create a new database and connect

** Image equivalent to creating an Excel file and opening Excel ** isolation_level = None is a setting that automatically saves the database if you forget the commit operation.

python


import sqlite3

dbname = ('test.db')#Database name.Set by db extension
conn = sqlite3.connect(dbname, isolation_level=None)#Create database, turn on auto-commit function

② Create a table

** Use the mouse to manipulate Excel, but sqlite manipulates rows with cursor objects **

python


cursor = conn.cursor() #Create cursor object

"""
-Create table Declare a table with a SQL statement called table name (data column you want to create)
* SQL instructions can be in uppercase or lowercase
・ This time, "id" is added to the test table.,name,date column(Column name)* This time date is a column called date of birth
-"If not exists" is the error prevention part. If the same table has already been created, an error will occur.
-There is no problem even if you do not specify the column type.
  ※NULL, INTEGER(integer), REAL(Floating point), TEXT(String), BLOB(binary)5 types
"""
sql = """CREATE TABLE IF NOT EXISTS test(id, name, date)"""

cursor.execute(sql)#Execute SQL statement with execute command
conn.commit()#Commit to database(Overwrite save in Excel. It is unnecessary because it is an automatic commit setting, but for the time being ...)

③ Get the created table name

In Excel, you can see the Sheet name created on the screen immediately, but of course you do not know SQL, so make an inquiry

python


#SQL function to get the table name in the database
sql = """SELECT name FROM sqlite_master WHERE TYPE='table'"""

for t in cursor.execute(sql):#Check all the tables created by the for statement
    print(t)

Execution result


('test',)

④-1 Store one row of records in the created table

** Data for each row is called a record in SQL ** Add one line with execute

python


"""
Use the insert statement to add a record.
In the case of python, it is basically described using "?" As a measure against the vulnerability to illegal SQL instructions called SQL injection.
"""
sql = """INSERT INTO test VALUES(?, ?, ?)"""#?Means that you will receive the value later

data = ((1, 'Taro', 19800810))#Specify the record to insert
cursor.execute(sql, data)#Execute SQL statement with execute command
conn.commit()#commit

④-2 Store multiple rows of records in the created table

Use execute many instead of execute

python


sql = """INSERT INTO test VALUES(?,?,?)"""

data = [
   (1, "Taro", 19800810),
   (2, "Bob", 19921015),
   (3, "Masa", 20050505),
   (4, "Jiro", 19910510),
   (5, "Satoshi", 19880117)
]
cursor.executemany(sql, data)#If you want to add multiple data, use execute many method
conn.commit()#commit

⑤-1 Extract all records in the created table

Use fetchall

python


"""
select *Refer to all data with, and specify from which table to call the data with from
fetch all rows of data with fetchall
"""
sql = """SELECT * FROM test"""
cursor.execute(sql)
print(cursor.fetchall())#Retrieve all records

Execution result(④-In case of 2)


[(1, "Taro", 19800810),(2, "Bob", 19921015),(3, "Masa", 20050505),(4, "Jiro", 19910510),(5, "Satoshi", 19880117)]

⑤-2 Extract all records in the created table line by row

Use fetchone. The execution result is the same as ⑤-1.

python


select_sql = """SELECT * FROM test"""
cursor.execute(select_sql)

while True:
    result=cursor.fetchone()#Extract one line of data
    if result is None :#Loop withdrawal condition(When the data is completely extracted and empty)
        break #Break out of the loop
    
    print(result)

⑥ Delete the record

python


"""
Specify by writing the condition of the data you want to erase after where
Since the element in the first row of this table is id, specify the data with id 2 as an example.
"""

cursor.execute('delete from test where id=?', (2,))
conn.commit()#commit

cursor.execute('select * from test')
print(cursor.fetchall())

Execution result(④-In case of 2)


[(1, "Taro", 19800810),(3, "Masa", 20050505),(4, "Jiro", 19910510),(5, "Satoshi", 19880117)]

⑦ Change the name of the created table

python


"""
ALTER TABLE Table name before change RENAME TO Table name after change
"""
sql = """ALTER TABLE test RENAME TO test1"""

#Execute instruction
conn.execute(sql)
conn.commit()#commit

⑧ Delete the created table

python


"""
DROP if exists TABLE drop table name
"""
sql = """DROP if exists TABLE test1"""

#Execute instruction
conn.execute(sql)
conn.commit()#commit

⑨ Block the database connection

python


#Close the DB connection when the work is completed
conn.close()

⑩ (Bonus) Read the created DB with Pandas after specifying the table

python


import pandas as pd

dbname = "test.db"
conn = sqlite3.connect(dbname)

#read db_Read as pandas using sql.
df = pd.read_sql('SELECT * FROM test', conn)

df.head()

Display the result of ⑥ in Pandas

id name date
0 1 Taro 19800810
1 3 Masa 20050505
2 4 Jiro 19910510
3 5 Satoshi 19880117

3. Finally

** This article is the first article for investment utilization. I will continue to write Python articles that are useful for investment-related matters ** ** If you think it will be useful even a little, it will be encouraging if you can stock LGTM! ** **

Recommended Posts

Easily handle databases with Python (SQLite3)
Easily handle lists with python + sqlite3
Trying to handle SQLite3 with Python [Note]
Handle Excel with python
Easily beep with python
Easily serverless with Python with chalice
[Tips] Handle Athena with Python
Manipulate various databases with Python
[Python] [SQLite3] Operate SQLite with Python (Basic)
Handle Base91 keys with python + redis.
Easily implement subcommands with python click
Handle Excel CSV files with Python
[Python] Collect images easily with icrawler!
Easily post to twitter with Python 3
Put protocol buffers into sqlite with python
Save / load in-memory DB with python sqlite3
Easily download mp3 / mp4 with python and youtube-dl!
FizzBuzz with Python3
Scraping with Python
Scraping with Python
Python with Go
Twilio with Python
Integrate with Python
Play with 2016-Python
AES256 with python
Tested with Python
python starts with ()
with syntax (Python)
Sqlite in python
Bingo with python
Zundokokiyoshi with python
Excel with Python
Microcomputer with Python
Cast with python
I tried hundreds of millions of SQLite with python
You can easily create a GUI with Python
Handle zip files with Japanese filenames in Python 3
[Rust / Python] Handle numpy with PyO3 (August 2020 version)
Getting started with AWS IoT easily in Python
Create a Python console application easily with Click
[Python] How to handle Japanese characters with openCV
How to handle datetime type in python sqlite3
Serial communication with Python
Zip, unzip with python
Django 1.11 started with Python3.6
Python with eclipse + PyDev.
Socket communication with Python
Data analysis with python 2
Scraping with Python (preparation)
Try scraping with Python.
Easily daemonized with Supervisor
Learning Python with ChemTHEATER 03
Sequential search with Python
"Object-oriented" learning with python
Handling yaml with python
Solve AtCoder 167 with python
Serial communication with python
[Python] Use JSON with Python
Learning Python with ChemTHEATER 05-1
Learn Python with ChemTHEATER
Run prepDE.py with python3