Beginners Guide to SQLALchemy In Python For Database Operations
SQLALchemy is an open-source pythonic way of interacting with databases that was released under the MIT license. It is a cross-platform software implemented in python and gives flexible usage for application developers. Now no need of learning Database languages to work with Database, by using a python programming language we can perform all database operations more efficiently. By using SQLALchemy instead of learning a database programming language we can directly write queries for creating, manipulations, retrieving data, updating records using simple python scripts. Topics we cover in this article Installing SQLALchemy Creating a table using SQLALchemy Inserting entities using SQLALchemy Deleting entities using SQLALchemy Updating records using SQLALchemy Retrieving Records from the table Installing and using SQLALchemy Using the pip method, we can install the SQLALchemy and by importing it, we can use it further in the program. pip install sqlalchemyimport sqlalchemy as db Hands-on implementation of  SQLALchemy in python Now, we will implement the SQLAlchemy for database operations in the below sections. Creating a Table In the below code snippet, we will create a table using SQLALchemy using python. from sqlalchemy import create_engineengine = create_engine('sqlite:///college.db', echo = True)from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Stringengine = 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),)meta.create_all(engine)print("these are columns in our table %s" %(students.columns.keys()))Output:- Here, we created a table named students with three columns id, name, lastname, and we are displaying the list of columns in the tables. Inserting data to the table Using the below code snippet we will insert data into the table from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Stringengine = 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), )ins = students.insert()ins = students.insert().values(name = 'prudhvi', lastname = 'varma')conn = engine.connect()result = conn.execute(ins) We can see in the sixth line of output that we have added the name: prudhvi and last name: varma into the table. In the below code snippet, we are inserting more values into the table. Here you can add your own entities and store them in the database. conn.execute(students.insert(), [   {'name':'Bhaskar', 'lastname' : 'guptha'},   {'name':'vibhav','lastname' : 'kumar'},   {'name':'prudhvi','lastname' : 'varma'},   {'name':'manoj','lastname' : 'varma'},]) Deleting data from tables In the below code snippet, let us delete one person from the table. So based on the name we will filter the person and delete it from the table. from sqlalchemy.sql.expression import updatefrom sqlalchemy import create_engine, MetaData, Table, Column, Integer, Stringengine = 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.name == 'manoj')conn.execute(stmt)s = students.select()conn.execute(s).fetchall() In the above output, we removed the row, filtered by his name that was ‘manoj’. Updating records in the tables In the below code snippet, we will update the details of the student. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Stringengine = 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.update().where(students.c.name=='vibhav').values(name='Dr.vaibhav')conn.execute(stmt)s = students.select()conn.execute(s).fetchall() In the output, we updated the name Vaibhav to Dr.vaibhav Retrieving Records from the table In the below code snippet we are going to display the row based on  the name of the student s = students.select().where(students.c.name=="prudhvi")result = conn.execute(s)for row in result:   print (row) In the output, we filtered the student with name ‘prudhvi’. Using the below code snippet we are going to display the row filtered by id. s = students.select().where(students.c.id < 3)result = conn.execute(s)for row in result:   print (row) In the output, we filtered the students who have id < 3 Conclusion In the above implementation, we learned how to create tables, insert entities, update records, delete rows, retrieving records using SQLALchemy in the python. As we could see that it is very easy to use with no database API required to perform the database operations. The above code snippets are implemented in the Google Colab environment. These implementations can also be performed in any python IDE’s.
By using SQLALchemy instead of learning a database programming language we can directly write queries for creating, manipulations, retrieving data, updating records using simple python scripts.
Beginners Guide to SQLALchemy In Python For Database Operations - creating, updating, retrieving data, updating records using python scripts
["Python", "python database gui", "what is database"]
["Go", "API", "TPU", "programming_languages:R", "AI", "R", "Python", "Colab", "programming_languages:Python", "SQL", "what is database", "python database gui"]
["AI", "Colab", "TPU", "Python", "R", "SQL", "Go", "API", "programming_languages:Python", "programming_languages:R"]
AI enthusiast, Currently working with Analytics India Magazine. I have experience of working with  Machine learning, Deep learning real-time problems, Neural networks, structuring and machine learning projects. I am a Computer Vision researcher and I am Interested in solving real-time computer vision problems.