[For beginners] Script within 10 lines (4. Connection from python to sqlite3)

Script to get and display sqlite3 data from pytho3 with cursor sqlit3 can be searched by sql statement even if you run the server, you can use it in other environments by copying the file, and recently the number of functions has increased, and it seems that you can also use analysis functions. You can connect to sqlite3 from python without preparing a special library etc. with python. However, the standard interface of sqlite3 is simple and good, but if you make a mistake in the middle of a slightly long sentence, to be honest, you may find it troublesome to input. A shell script is fine, but I thought, "Is it a little easier to search if I make some script?", So I would like to post a connection from python to sqlite3.

Script to get and display sqlite3 data from pytho3 with cursor

【environment】 Linux: DISTRIB_DESCRIPTION="Netrunner Core 20.01" python: 3.7 conda: 4.8.3 sqlite3: 3.31.1

By the way, the distribution that ran this script is debian, although you may not be familiar with it. Also, the virtual environment included conda, which I used before, but recently I moved to venv with pip3, so I was confused for a moment to remember the basic operation.

For the data, I would like to use the file downloaded from fred with pandase-datareader and written to csv.

The data is pre-imported into sqlite3.

The code ran from the terminal.

python3.7

#Connect to sqlite3 from python
#Get and display all data
#Database name= './dexjpus_20200501.sqlite3'
#table name= 't_dexjpus'
# 
import sqlite3

#Database connection definition
dbname = './dexjpus_20200501.sqlite3'
conn = sqlite3.connect(dbname)
cur = conn.cursor()

#df = pd.read_sql( 'select * from t_dexjpus;' ,conn)
#Get all data
all_data = 'select  * from t_dexjpus'

#Display the acquired data line by line
for row in cur.execute(all_data):
        print(row)

cur.close()
conn.close()

** 1. Definition of database connection ** For sqlite3, describe the file name where the data was imported This database was created as "dexjpus_20200501.sqlite3" in the current directory, so in this script, Therefore, the database name is "dexjpus_20200501.sqlite3".

python3.7

dbname = './dexjpus_20200501.sqlite3'
conn = sqlite3.connect(dbname)
cur = conn.cursor()

** 2. Data acquisition ** Stored "'select * from table name'" in a variable. The table name is "t_dexjpus", so it is assumed that all data will be acquired. all_data ='select * from table name'

python3.7

#Get all data
all_data = 'select  * from t_dexjpus'

** 3. Display the acquired data and close the connection. ** ** cur.execute (the variable that stored the contents of the sql statement) In this script, "cur.execute (all_data)" Repeat with for statement

python3.7

#Display the acquired data line by line
for row in cur.execute(all_data):
        print(row)

cur.close()
conn.close()

** This is the connection from python to sqlite3. ** **