1. Introduction

While the previous article talked about the two most common ways Python handles Mysql databases, this article continues with another popular database: Sqlite

Sqlite is an embedded database. A database is a small file, written in C and often integrated into mobile applications

In fact, Python has the sqlite3 module built in, which allows you to operate directly on Sqlite databases without having to install any dependencies

2. Prepare

Similar to how Python operates Mysql, Sqlite can be operated in the following two ways:

  • Sqlite3 + native SQL

  • SQLAlchemy + ORM

3. Sqlite3 + native SQL

Since Python has the sqlite3 module built in, you can use it directly here

Sqlite3 import sqlite3Copy the code

First, we use sqlite3’s Connnect () method to create a database connection object, and if the database does not exist, a new database file is automatically created in the corresponding directory

Self.conn = sqlite3.connect(self.path_db) self.conn = sqlite3.connect(self.path_db)Copy the code

Then, a cursor instance that manipulates the database is obtained through the database connection object

Self.cursor = self.conn.cursor()Copy the code

Next, execute the SQL statement that creates the table using the database connection object, creating a new table within the database

SQL_CREATE_TABLE = "" CREATE TABLE IF NOT EXISTS PEOPLE (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL); Def create_db_table(self): """ return: """ self.conn.execute(SQL_CREATE_TABLE)Copy the code

Next, we manipulate the table by adding, deleting, modifying, and checking

1, the new

For example, add a single item of data and add multiple items of data

For single data inserts, simply write an INSERT SQL statement, execute the execute(SQL) method of the above database connection object as a parameter, and commit the data to the database using the commit() method of the database connection object

SQL_INSERT_ONE_DATA = "INSERT INTO PEOPLE(id,name,age) VALUES(3,'xag',23); Def insert_one(self): """ """ try: Self.conn.execute (SQL_INSERT_ONE_DATA) # self.conn.mit () except Exception as e: Rollback () print(' insert record failed, rollback ~')Copy the code

Note that the insert operation often causes new exceptions due to the primary key, so you need to catch exceptions and perform rollback operations

Using the executemany() method of the database connection object, passing in the inserted SQL statement and a list of location variables, you can insert more than one data at a time

SQL_INSERT_MANY_DATA = 'INSERT INTO PEOPLE (id,name,age) VALUES(? ,? ,?) ; '# self to insert data. The data = [(4,' zhang SAN, 11), (5, 'li si, 12), (6,' Cathy '13)] def insert_many (self, data) : "" "" "add multiple data" try: self.conn.executemany(SQL_INSERT_MANY_DATA, data) self.conn.commit() except Exception as e: Rollback () print(' insert multiple records failed, rollback ~')Copy the code

2, the query

The query is divided into two steps:

  • SQL statements that execute queries through cursor objects

  • Call the cursor object’s method to get the query result

Such as:

To get all the data, use the fetchAll () method of the cursor object

To get the first data that meets the criteria, use the fetchone() method

In addition, fetchmany(num) can query a fixed amount of data

Sql_one_data = "SELECT * FROM PEOPLE WHERE id={}" def query_one(self, id): "" :return: Self.cursor.execute (sql_query_one_data.format (id)) # fetchOne (): fetchall() : Fetchall () print(type(result)) print(result)Copy the code

3, update,

Similar to the new operation, the update operation is to execute the updated SQL statement through the database connection object, and finally perform the commit operation to actually update the data into the data table

Take updating a record as an example

Sql_one_data = "UPDATE PEOPLE SET NAME = '{}',AGE={} where id ={} "def update_one(self, id, NAME,AGE): "" modify a record :param ID: :param name: :param age: :return: """ sql_update = SQL_UPDATE_ONE_DATA.format(name, age, id) print(sql_update) self.conn.execute(sql_update) self.conn.commit()Copy the code

4, delete,

The deletion operation is similar to the query and add operations. You only need to execute the SQL statement to be deleted

Take deleting a record as an example

SQL_DEL_ONE_DATA = "DELETE FROM PEOPLE where id ={}" def del_one(self, id): Sql_del = SQL_del_one_data.format (id) self.conn.execute(sql_del) self.conn.mit ()Copy the code

Finally, we also need to free the cursor object and the database connection object

Def cursor.close() self.conn.close() def cursor.close() self.conn.close()Copy the code

4. SQLAlchemy + ORM

Using SQLAlchemy to operate sqLite databases also requires the installation of dependent libraries

Pip3 install sqlalchemyCopy the code

Create a Base class Base through the built-in method Declarative_base ()

Then, you define a subclass of the Base class that internally specifies the name of the table and the fields of the table by defining static variables

from sqlalchemy import Column, Integer, String, Create_engine from sqlalchemy. Ext. Declarative import declarative_base Base class # Base = declarative_base () # custom table class People(Base): __tablename__ = 'people' Primary_key =True) name = Column(String) age = Column(Integer) def __repr__(self): """ return "<People(id:{},name:{},age:{})".format(self.id, self.name, self.age)Copy the code

Next, the database connection object is created through SQLAlchemy’s create_engine(SQLite database path) method

The format is sqlite:/// relative path of the database

Engine = create_engine('sqlite:///./xh.db', echo=True)Copy the code

Finally, the database engine creates a table structure in the database and instantiates a database session object

PS: database session object built-in method is very convenient for us to add, delete, change and check operations

Create table structure # checkfirst Create_all (engine, checkfirst=True) # create session self.session = sessionmaker(bind=engine)()Copy the code

In this way, all the preparatory work has been completed, and then you can add, delete, change and check the operation

1, the new

For example, adding one or more records corresponds to the add() and add_all() methods of the session object

To add a new operation to a record, you simply need to instantiate a People object, execute the add(instance) and commit() methods of the session object above, and insert the data into the table

def add_one_data(self): People = people (name='xag1', age=24) self.session.add(people) Self.session.mit ()Copy the code

If you need to insert more than one data at a time, just call add_all(list data)

Def add_datas(self, data): """ return: """ self.session.add_all(data) self.session.mit ()Copy the code

2, the query

The operation of querying a data table corresponds to the query() method of the session object

You can also use cascading methods of all(), first(), and filter_by(constraints) to restrict the data to be queried

Take the example of querying all records and querying one record by ID

Def query_one_data(self, id): """ def query_one_data(self, id): "" # select * from id; People = self.session.query(people).filter_by(id=id).first() print(people) print(type(people)) def query_all(self): Return: """ peoples = self.session.query(People).all() print(peoples) print(type(peoples))Copy the code

3, update,

The general practice of update operation is as follows:

  • Query Queries the object to be updated

  • Update the data in the object directly

  • Commit the changes using the session object to complete the update operation

Def update1(self, id, name, age): """ def update1(self, id, name, age): """ "" # update step: People_temp = self.session.query(People).filter_by(id=id).first() # people_temp = name Self.session.mit ()Copy the code

It should be noted that the above update operation can also be abbreviated using the built-in update() method

Def update2(self, id, name, age): "" def update2(self, id, name, age): "" """ self.session.query(People).filter_by(id=id).update({People.name: name, People.age: age}) self.session.commit()Copy the code

4, delete,

Like the update operation, the delete operation can be implemented in two ways

The first approach is to query, then delete, and finally submit a session to complete the deletion operation

Take deleting a record by ID as an example:

Def del_one_data1(self, id): """ def one_data1(self, id): "" people_temp = self.session.query(People).filter_by(id=id).first() Self.session.delete (people_temp) self.session.mit () else: print(self.session.mit () else: print(self.session.mit ()) ')Copy the code

Note that the query result must be null or not. Otherwise, an exception may be thrown

Alternatively, you can simply abbreviate the above delete operation using a cascade function

Def del_one_data2(self, id): """ def one_data2(self, id): """ Self.session.query (People).filter_by(id=id).first().delete() except Exception as e: print(' delete failed ')Copy the code

Again, this deletion requires catching exceptions to avoid the null result of the query

Finally, when all is done, we need to end the session and destroy the database engine

Def teardown(self): """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ """ ""Copy the code

5. The last

This article explains two ways to use SqLite in Python by adding, deleting, modifying, and querying a table

I have uploaded all the source code to the background, follow the public account “AirPython” reply “dball” can get all the source code

If you think the article is good, please like, share, leave a message, because this will be my continuous output of more high-quality articles the strongest power!