Sqlite in python

I wanted to use a database with Python, so I touched the sqlite3 module included in the standard library.

Click here for sample code.

# -*- coding: utf-8 -*-

import sqlite3
from contextlib import closing

dbname = 'database.db'

with closing(sqlite3.connect(dbname)) as conn:
    c = conn.cursor()

    #Execute SQL statement with execute method
    create_table = '''create table users (id int, name varchar(64),
                      age int, gender varchar(32))'''
    c.execute(create_table)

    #When setting a value in a SQL statement, do not use Python's format method etc.
    #Where you want to set?In the second argument of the execute method?The value that applies to
    #Pass it as a tuple.
    sql = 'insert into users (id, name, age, gender) values (?,?,?,?)'
    user = (1, 'Taro', 20, 'male')
    c.execute(sql, user)

    #If you want to execute multiple SQL statements at once, create a list of tuples and then
    #executemany method is executed
    insert_sql = 'insert into users (id, name, age, gender) values (?,?,?,?)'
    users = [
        (2, 'Shota', 54, 'male'),
        (3, 'Nana', 40, 'female'),
        (4, 'Tooru', 78, 'male'),
        (5, 'Saki', 31, 'female')
    ]
    c.executemany(insert_sql, users)
    conn.commit()

    select_sql = 'select * from users'
    for row in c.execute(select_sql):
        print(row)

Below is a brief explanation of the sample code.

Connect to database

To connect to the database, use the sqlite3.connect () method.

conn = sqlite3.connect(dbname)

The sqlite3.connect () method creates a Connection object. To execute the SQL statement, you need to create another Cursor object from the Connection object.

	c = conn.cursor()

Various commands can be executed on the database by using this Cursor object.

SQL execution

To execute the SQL statement, use the ʻexecute ()` method of the Cursor object.

	c.execute(sql[, parameters])

When? Is embedded in the SQL statement of the first argument, the value to be set in? Is passed as a tuple in the second argument.

Example

	user = (1, 'Taro', 20, 'male')
	c.execute('insert into users (id, name, age, gender) values (?,?,?,?)', user)

By doing this, the values in the tuple are applied to the? Part of SQL, and finally the following SQL statement is executed.

	insert into users (id, name, age, gender) values (1, 'Taro', 20, 'male')

If you want to execute multiple SQLs at once, use the ʻexecutemany ()` method and pass a list of tuples as the second argument (see sample code).

Save changes to database

Always call the commit () method after adding / deleting to the database with the ʻexecute () method or the ʻexecute many () method. If you close the database without calling this method, your changes will not be saved.

	conn.commit()

Close database

Don't forget to close the database connection at the end of the program. Use the close () method for this. ** Note that this method does not automatically call commit (). ** **

	conn.close()

Recommended Posts

Sqlite in python
Python in optimization
CURL in python
Metaprogramming in Python
Python 3.3 in Anaconda
Geocoding in python
Meta-analysis in Python
Unittest in python
How to use SQLite in Python
Epoch in Python
Discord in Python
Sudoku in Python
DCI in Python
quicksort in python
nCr in python
N-Gram in Python
Programming in python
Plink in Python
Constant in python
Lifegame in Python.
FizzBuzz in Python
Foreign Key in Python SQLite [Note]
StepAIC in Python
N-gram in python
LINE-Bot [0] in Python
Csv in python
Disassemble in Python
Reflection in Python
Constant in python
nCr in Python.
format in python
Scons in Python3
Puyo Puyo in python
python in virtualenv
PPAP in Python
Quad-tree in Python
Reflection in Python
Chemistry in Python
Hashable in python
DirectLiNGAM in Python
LiNGAM in Python
Flatten in python
flatten in python
Daily AtCoder # 36 in Python
Clustering text in Python
Implement Enigma in python
Daily AtCoder # 32 in Python
Daily AtCoder # 6 in Python
Daily AtCoder # 18 in Python
Edit fonts in Python
Singleton pattern in Python
File operations in Python
Read DXF in python
Daily AtCoder # 53 in Python
Key input in Python
Use config.ini in Python
Solve ABC168D in Python
Daily AtCoder # 7 in Python
LU decomposition in Python
One liner in Python
Simple gRPC in Python