Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

What is SQLAlchemy?

Sqlalchemy is an excellent open source ORM framework for Python. It provides a fast and convenient API for developers, which can improve development efficiency and allow developers to focus on business code development instead of wasting time on database maintenance. Today we are going to take a look at the basics of SQLalchemy.

What is SQLite?

SQLite is an ultra-light relational database, which is essentially a.db file that can be used quickly and easily in a test environment or in a production environment. All in all, it’s an excellent database.

The installation

Installed sqlalchemy

pip install sqlalchemy
Copy the code

Install SQLitebrowser

Download address: http://www.sqlitebrowser.org/dl/Copy the code

Choose the right download.

example

Create databases & tables

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Boolean,Column,Integer,String


# define database path (none will be created automatically)
SQLiteURL = 'sqlite:///test.db'

Create engine, database driver information
engine = create_engine(
    url=SQLiteURL,
    echo=True.# Open sqlAlchemy ORM process details
    connect_args={
        'check_same_thread':False   # Multithreading})Table base class (used to define table structure)
Base = declarative_base()

Define the User table structure

class User(Base) :
    The User object corresponds to the table Users
    __tablename__='users'
    my_id = Column(Integer,primary_key=True,index=True)
    name = Column(String(32),unique=True,index=True)
    passwd = Column(String(32),index=True)
    is_active = Column(Boolean,default=True)

# to create table
# checkFirst =True The default is also True, that is, the database will not be created if it exists
Base.metadata.create_all(engine, checkfirst=True)
Copy the code

Execute the above code:

PS C:\Users\xx\Desktop\study\02> python.\orm.py 2021-07-12 17:23:06,010 INFO SQLalchemy.engine. 2021-07-12 17:23:06,010 INFO SQLalchemy.engine.Engine PRAGMA main.table_info(" Users ") 2021-07-12 17:23:06,011 INFO [RAW SQL] () 2021-07-12 17:23:06,011 INFO SQLalchemy.engine.Engine PRAGMA Temp. Table_info (" Users ") 2021-07-12 17:23:06,011 INFO SQLAlchemy.engine.Engine [RAW SQL] () 2021-07-12 17:23:06/012 INFO  sqlalchemy.engine.Engine CREATE TABLE users ( my_id INTEGER NOT NULL, name VARCHAR(32), passwd VARCHAR(32), is_active BOOLEAN, PRIMARY KEY (my_id)) 2021-07-12 17:23:06,012 INFO SQLAlchemy. engine.Engine [no KEY 0.00047s] () 2021-07-12 17:23:06s,026 INFO SQLalchemy.engine.Engine CREATE INDEX IX_users_my_id ON Users (my_id) 2021-07-12 17:23:06.027 INFO Sqlalchemy.engine.Engine [no key 0.00029s] () 2021-07-12 17:23:06,030 INFO SQLalchemy.engine.Engine CREATE INDEX Ix_users_passwd ON Users (passwd) 2021-07-12 17:23:06,030 INFO SQLalchemy.engine.Engine [no key 0.00025s] () 2021-07-12 17:23:06,033 INFO SQLalchemy.engine.Engine CREATE UNIQUE INDEX IX_users_name ON users (name) 2021-07-12 17:23:06,034 INFO sqlalchemy.engine.Engine [no key 0.00040s] () 2021-07-12 17:23:06.037 INFO SQLalchemy.engine.Engine COMMIT PS C:\Users\xx\Desktop\study\02>Copy the code

Test.db = test.db; test.db = test.db

Test. db view in SQLitebrowser:

Data creation Add

Create session object (link to database)
SessionLocal = sessionmaker(
    bind=engine,
    autoflush=False,
    autocommit=False
)

Create session instance (instantiation)
db = SessionLocal()

Create a new User instance

phyger = User(
    my_id = 1,
    name = 'phyger',
    passwd = 'pwd@123'
)

Insert phyger instance into users table

db.add(phyger)

Insert data after commit
db.commit()

Close the database connection
db.close()
Copy the code

After executing the code, go to SQLitebrowser to see:

Data Query Query

Create session object (link to database)
SessionLocal = sessionmaker(
    bind=engine,
    autoflush=False,
    autocommit=False
)

Create session instance (instantiation)
db = SessionLocal()

# query data

res = db.query(User).all(a)print(res)

# conditional query
user1 = db.query(User).filter_by(my_id=1).first()
print(user1)

Close the database connection
db.close()
Copy the code

Code execution result:

You will find that the log prints the actual SQL statement executed and displays the query information. See the official documentation for more filtering criteria.

Data update

Create session object (link to database)
SessionLocal = sessionmaker(
    bind=engine,
    autoflush=False,
    autocommit=False
)

Create session instance (instantiation)
db = SessionLocal()

# update data

user1.name = 'flyGg'

db.commit()

# query after update
user2 = db.query(User).filter_by(my_id=1).first()

print(user2.name)

Close the database connection
db.close()
Copy the code

Delete data Delete

For demonstration purposes, we have added an information phyger2

Create session object (link to database)
SessionLocal = sessionmaker(
    bind=engine,
    autoflush=False,
    autocommit=False
)

Create session instance (instantiation)
db = SessionLocal()

# delete data

user3 = db.query(User).filter_by(my_id=1).first()
db.delete(user3)
db.commit()

Close the database connection
db.close()
Copy the code

Reference: www.cnblogs.com/lsdb/p/9835…

That’s all for today, thank you for reading, and we’ll see you next time.