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()