DEV Community

atenliao
atenliao

Posted on

Use SQLAlchemy to CRUD SQLite

#python

What is SQLAIchemy? It is python toolkit to interact with SQL database and query your data, which means you can write your query in form of string or chain objects in python to build high-perform SQL backend or application.

  1. Install Python 3.8 on Ubuntu 22.04 LTS
  2. update system 
sudo apt update && sudo apt upgrade
Enter fullscreen mode Exit fullscreen mode
  1. add PPA for Python 3.8 
sudo apt install software-properties-common
sudo add-apt-repository ppa:deadsnakes/ppa
Enter fullscreen mode Exit fullscreen mode
  1. Check python version 
sudo apt-cache policy python3.8
Enter fullscreen mode Exit fullscreen mode
  1. install Python 3.8 on Ubuntu or Ubuntu on WSL2 
sudo apt install python3.8
Enter fullscreen mode Exit fullscreen mode
  1. set default version 
sudo update-alternatives --install /usr/bin/python python /usr/bin/python3.8 2
Enter fullscreen mode Exit fullscreen mode 
sudo update-alternatives --install /usr/bin/python python /usr/bin/python3.10 3
Enter fullscreen mode Exit fullscreen mode
  1. Install Pipenv Globally
  2. In Terminal upgrade pip 
python3.8 -m pip install pip --upgrade
Enter fullscreen mode Exit fullscreen mode
  1. install pipenv 
python3.8 -m pip install pipenv
Enter fullscreen mode Exit fullscreen mode

3. Navigate to your project to install SQLAIchemy

cd /Project
python3.8 -m pipenv install sqlalchemy=="1.4.41" alembic
Enter fullscreen mode Exit fullscreen mode

4.Connecting the database

from sqlaichemy as mydb
engine = mydb.create_engine(sqlite:///empolyee_database.db)
connect = engine.connect()
Enter fullscreen mode Exit fullscreen mode

5.Create Table

metadata = mydb.MetaData()
Employee = mydb.Table('Employee',metadata,
           mydb.Column('Id',mydb.Integer(),primary_key=True),
           mydb.Column('Name',mydb.string(255), nullable=False),
           mydb.Column('department', mydb.string(50), nullable=False))
           mydb.Column('Time', mydb.string(50))
metadata.create_all(engine)
Enter fullscreen mode Exit fullscreen mode

6.Insert One

query=mydb.insert(Employee).values(Id=1, Name="Jone",department="Pick", Time='9 hours')
Recult=connect.execute(query)

display = connect.execute(Employee.select()).fetchall()
print(display)
Enter fullscreen mode Exit fullscreen mode

7.update data in database

mydb.update(Employee).values(Time = 'where hours').where(department='pack flow')
Enter fullscreen mode Exit fullscreen mode

8.delete Table

mydb.delete(Employee).where(id = 3)
Enter fullscreen mode Exit fullscreen mode

9.View table

employee= mydb.Table('employee', metadata,autoload=True, autoload_with=engine)
print(employee.columns.keys())
Enter fullscreen mode Exit fullscreen mode

To conclue, this is simple way to use sqlaichemy to access sql database. we can use it in our simple backend for web application

Top comments (0)

Read next

dev007777 profile image

Python beats Javascript, Next.js Leap & the AI Coding Wars

TheDev -

talenttinaapi profile image

Exploring Test Automation in Embedded Systems Testing

talent -

hyperkai profile image

eq and ne in PyTorch

Super Kai (Kazuya Ito) -

alexmercedcoder profile image

Leveraging Python's Pattern Matching and Comprehensions for Data Analytics

Alex Merced -