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.
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.
** 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
** 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 ...)
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',)
** 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
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
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)]
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)
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)]
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
python
"""
DROP if exists TABLE drop table name
"""
sql = """DROP if exists TABLE test1"""
#Execute instruction
conn.execute(sql)
conn.commit()#commit
python
#Close the DB connection when the work is completed
conn.close()
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 |
** 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