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

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

If you use various libraries with python, "It's convenient because you can do a little thing with a little code, and you can make a little script with a little 5 steps." I thought, so I may come up with python and other commands that are just listed, but I will post a script of about 10 lines on an irregular basis.

As ** 4th **, I would like to post a connection from python to sqlite3. </ font>

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

Recommended Posts

[For beginners] Script within 10 lines (4. Connection from python to sqlite3)
[For beginners] Script within 10 lines (1.folium)
[For beginners] Script within 10 lines (7. Script that outputs csv from sqlite3 table with bash
Connect to sqlite from python
~ Tips for beginners to Python ③ ~
[For beginners] Script within 10 lines (8. Plot map with folium [2]
How to run a Python program from within a shell script
[For beginners] Script within 10 lines (3. Data acquisition / csv conversion with datareader)
Python> Output numbers from 1 to 100, 501 to 600> For csv
Tips for Python beginners to use the Scikit-image example for themselves 9 Use from C
Procedure from AWS CDK (Python) development to AWS resource construction * For beginners of development
Memo # 4 for Python beginners to read "Detailed Python Grammar"
The fastest way for beginners to master Python
Python for super beginners Python for super beginners # Easy to get angry
Memo # 3 for Python beginners to read "Detailed Python Grammar"
~ Tips for Python beginners from Pythonista with love ① ~
Try to calculate RPN in Python (for beginners)
Memo # 2 for Python beginners to read "Detailed Python Grammar"
Memo # 7 for Python beginners to read "Detailed Python Grammar"
Introduction to Programming (Python) TA Tendency for beginners
Memo # 6 for Python beginners to read "Detailed Python Grammar"
How to make Python faster for beginners [numpy]
~ Tips for Python beginners from Pythonista with love ② ~
Memo # 5 for Python beginners to read "Detailed Python Grammar"
Changes from Python 3.0 to Python 3.5
Changes from Python 2 to Python 3.0
python textbook for beginners
OpenCV for Python beginners
Script within 10 lines (2.vimrc)
[For beginners] How to use say command in python!
How to convert Python # type for Python super beginners: str
Python script to create a JSON file from a CSV file
Python # How to check type and type for super beginners
Post from Python to Slack
Tips for Python beginners to use Scikit-image examples for themselves 4 Use GUI
Convert from Pandas DataFrame to System.Data.DataTable using Python for .NET
Cheating from PHP to Python
Learning flow for Python beginners
Run illustrator script from python
Anaconda updated from 4.2.0 to 4.3.0 (python3.5 updated to python3.6)
How to learn TensorFlow for liberal arts and Python beginners
Python3 environment construction (for beginners)
3 Reasons Beginners to Start Python
Python #function 2 for super beginners
How to convert Python # type for Python super beginners: int, float
Introduction to Python for VBA users-Calling Python from Excel with xlwings-
Switch from python2.7 to python3.6 (centos7)
Basic Python grammar for beginners
Tips for Python beginners to use the Scikit-image example for themselves
100 Pandas knocks for Python beginners
Python for super beginners Python #functions 1
[Python] Introduction to graph creation using coronavirus data [For beginners]
Python #list for super beginners
I tried changing the python script from 2.7.11 to 3.6.0 on windows10
Introduction to Python For, While
(Translation) Native connection from Python to Hadoop file system (HDFS)
8 services that even beginners can learn Python (from beginners to advanced users)
From building a Python environment for inexperienced people to Hello world
How to avoid duplication of data when inputting from Python to SQLite.
[Pandas] I tried to analyze sales data with Python [For beginners]
Atom: Note for Indentation Error when copying Python script to shell