SQLAlchemy Library Functions in Python

SQLAlchemy is a SQL toolkit and Object Relational Mapper (ORM). It is written in Python and gives power and flexibility of SQL to an application developer. It is an open source and cross-platform software released under MIT license is knows as SQLAlchemy. SQLAlchemy was first release in February 2006

Installation

There are two ways to install SQLAlchemy

1. The most efficient and easiest way is by using the Python Package Manager . This can be easily done by typing the following command in the terminal.
   • pip install sqlalchemy.
2. In the case of anaconda distribution of Python or if you are using this particular platform. This can be easily done by typing the following command in the terminal.
   • conda install -c anaconda sqlalchemy.

Connecting to the Database

This can be easily done by typing the following command

import sqlalchemy as db

engine = db.create_engine(‘dialect+driver://user:pass@host:port/db’)

Engine class

Some important methods of Engine class are following :-

Creating Table

SQLAlchemy Column object represents a column in a database table which is in turn represented by a Tableobject. Metadata contains definitions of tables and associated objects. Metadata is a collection of Table objects.

from sqlalchemy import MetaData
meta = MetaData()

Table class represents corresponding table in a database. The constructor takes the following parameters.

• Metadata :- This object that will hold this table.
• Column :- One and more objects of column class.

Data types

• Boolean
• Float
• Integer
• String
• Text
• Date
• Numeric
• DateTime
• Time
• BigInteger
• SmallInteger

To create a students table in School database

from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()

students = Table(
   'students', meta, 
   Column('idstudent', Integer, primary_key = True), 
   Column('fastname', String), 
   Column('lastname', String), 
)

The create_all() function uses the engine object to create all the defined table objects and stores the information in metadata.

meta.create_all(engine)

Complete code is given below 

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///school.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('idstudent', Integer, primary_key = True), 
   Column('fastname', String), 
   Column('lastname', String),
)
meta.create_all(engine)

SQL query for table creation as follows

CREATE TABLE students (
   idstudent INTEGER NOT NULL,
   fastname rahul,
   lastname verma,
   PRIMARY KEY (idstudent)
)

SQL Expressions

The expressions are construct using corresponding methods relative to target table object.

Insert Statement

 The INSERT statement is create by executing insert() method are follows

ins = students.insert()

The below code inserts details like student id, fastname, lastname.

'INSERT INTO students (id, fastname, lastname) VALUES (:id, :fastname, :lastname)'

Example

ins = users.insert().values(id=2, fastname = ‘swati’, lastname= kumari) str(ins) ‘INSERT INTO users (id, fastname, lastname) VALUES (:id, :fastname, :lastname)’

Output

Similarly, methods just like update(), delete() and select() create UPDATE, DELETE and SELECT expressions.

UPDATE Expression

Syntax

table.update().where(conditions).values(SET expressions)

Example of Update Expression

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///school.db', echo = True)
meta = MetaData()

students = Table(
   'students', 
   meta, 
   Column('idstudent', Integer, primary_key = True), 
   Column('fastname', String), 
   Column('lastname', String), 
)

conn = engine.connect()
stmt=students.update().where(students.c.lastname=='roy').values(lastname='raj')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

before

after

DELETE Expression

Syntax

stmt = students.delete()

Example of Data Expression

from sqlalchemy.sql.expression import update
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

conn = engine.connect()
stmt = students.delete().where(students.c.lastname == 'roy')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

If you have any queries regarding this article or if I have missed something on this topic, please feel free to add in the comment down below for the audience. See you guys in another article.

To know more about SQLALchemy Library Function please Wikipedia click here.

Stay Connected Stay Safe, Thank you.