[python]SQLAlchemy for select and insert objects into sqlite db.

SQLAlchemy is a python module that makes database operation easier for python scripters, the SQLAlchemy simplies the operation by replacing the SQL syntax with objects.

So here’s the SQL query for creating a table:

import sqlite3

create_table_query = "CREATE TABLE IF NOT EXISTS host_objects (host_obj_id INTEGER PRIMARY KEY," \
                     "name TEXT," \
                     "host TEXT," \
                     "desc TEXT)"
db = sqlite3.connect('objects.db')
cursor = db.cursor()
cursor.execute(create_table_query)
db.commit()
db.close()

This is the SQLAlchemy way of doing database.

from sqlalchemy import create_engine, Table, MetaData, Column, Integer, TEXT
from os.path import exists


# change according to the database file you need.
db_uri = 'sqlite:///objects.db'

engine = create_engine(db_uri)
metadata = MetaData(engine)
# host_obj_id column is the primary key and hence cannot be null.
# name and host columns also cannot be null, as these are required in ASA object creation.
# desc is an optional field which may or may not be available.
if not exists('objects.db'):
    host_objects = Table('host_objects', metadata,
                     Column('host_obj_id', Integer, primary_key=True),
                     Column('name', TEXT, nullable=False),
                     Column('host', TEXT, nullable=False),
                     Column('desc', TEXT))
    host_objects.create()
else:
    host_objects = Table('host_objects', metadata, autoload=True)

In SQLAlchemy there is no need to write SQL syntax, SQLAlchemy does it for us in the background, the result is the same.

Insert data into table with sqlite3 syntax.

for item in output:
    if item['type'] == 'host':
        host_objects_list = [item.get('name'), item.get('host'), item.get('desc', None)]
        cursor.execute(query, (host_objects_list[0],
                       host_objects_list[1],
                       host_objects_list[2]))

db.commit()

Below is SQLAlchemy way of doing insert.

i = host_objects.insert()
for result in results:
    if result['type'] == 'host':
        i.execute({'name': result.get('name'),
                  'host': result.get('host'),
                   'desc': result.get('desc', None)})

Below is the way of getting all data from table.

db_result = cursor.execute("SELECT * FROM host_objects")
db_result.fetchall()

Below is SQLAlchemy way of getting all data.

rs = host_objects.select().execute()
print(rs.fetchall())

Get data with WHERE condition in SQLAlchemy way, the below is to get the rows that has ‘ABC_Object_Name’ in ‘name’ column.

rs = host_objects.select(host_objects.c.name == 'ABC_Object_Name').execute()
print(rs.fetchall())