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.

• Operating environment

• OS : Windows10 pro

• Python : 3.8.3// Miniconda 4.9.1

• sqlite3：2.6.0

• (Administrative tool: DB Browser for SQLite * Convenient for viewing the contents immediately)

• It is not necessary to install the sqlite3 module. Included in the Python (2.5+) standard library.

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 **

• Cursor ≒ an image like an Excel line? ** Table refers to Sheet in Excel ** ** Save Excel by overwriting each time after the operation (commit) **

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

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! ** **