Handling SQLite database using Python

Table of Contents

Suppose that the file name of SQLite's database is mpos_S.sqlite, its table (like a sheet of Excel) name is mpos_S, and mpos_S contains column data. If the column name of water temperature data is tp, this column tp contains water temperature data. In order to handle it from Python, you need to import sqlite3.
First connect to the database and get the cursor cur.
[cc]
>>> import sqlite3
>>> conn = sqlite3.connect('mpos_S.sqlite')
>>> cur = conn.cursor()
[/cc]
Check information of columns.
[cc]
>>> cur.execute("PRAGMA TABLE_INFO(mpos_S)") ### Gets information about columns of table mpos_S
>>> cols = cur.fetchall() ### Information of a column is a tuple containing 6 elements.
>>> print(cols)
[/cc]
To get only a name of column, the 2nd element should be taken using list comprehension.
[cc]
>>> print([item[1] for item in cols])
[/cc]
Gets the values for the column tp. Multiple columns are selected by itemizing their names separating by comma.
[cc]
>>> cur.execute("SELECT tp FROM mpos_S") ### tp is the column name, mpos_S is table name.
>>> val = cur.fetchall() ### val is a list of tuple containing values.
>>> print(val)
[/cc]
Gets data in all the columns.
[cc]
>>> cur.execute("SELECT * FROM mpos_S") ### * means all the columns. mpos_S is table name.
>>> val = cur.fetchall() ### Extracted as a list of tuple
>>> print(val)
[/cc]
Always close the database when finished (Required).
[cc]
>>> cur.close()
>>> conn.close()
[/cc]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.