ORMise your DB operations!
ORM stands for Object Relational Mapping.
Now what is that?
Compared to traditional techniques of exchange between an object-oriented language and a relational database, ORM often reduces the amount of code that needs to be written.
There must be some kind of downside for this approach?
Disadvantages of ORM tools generally stem from the high level of abstraction obscuring what is actually happening in the implementation code. Also, heavy reliance on ORM software has been cited as a major factor in producing poorly designed databases
Now that diagram is just pulled from Internet that one is supposed to tell us about working structure of SQLAlchemy that is our main purpose to write this blog.
Let’s start from groud:
- We have Database. (of-course what else we should have, mangoes 😀 )
- DBAPI. (definitely needs otherwise how else we will make calls to our database for read and write operations )
- SQLAlchemy CORE
Before talking about SQLAlchemy CORE we should really talk about what SQLAlchemy people believe about ORMs.
SQLAlchemy’s overall approach to these problems is entirely different from that of most other SQL / ORM tools, rooted in a so-called complimentarity- oriented approach; instead of hiding away SQL and object relational details behind a wall of automation, all processes are fully exposed within a series of composable, transparent tools. The library takes on the job of automating redundant tasks while the developer remains in control of how the database is organized and how SQL is constructed.
The main goal of SQLAlchemy is to change the way you think about databases and SQL!
That is true, when you start working with it you feel like you are controlling your DB with your crazy logics not from DB quiries. (That looks like bit freedom it could crash my DB if I have no Idea what I am doing but I guess that is the beauty of it. 😉 😀 )
Core contains the methods those are integrated with DB API to create Connection with DB,handle sessions,create and delete tables-rows-columns,insertion,execution,selection,accessing values from IDs comes into place. It really feels like you are just writing your favourite language while handling DB operations in place. Moreover every operation just works on the fly unless you have really messed up your DB like breaking connection while reading/writing process,declaring wrong types,messing with fields or just dumping data without even parsing-cleaning it bit. Exception handling really comes into place when you interact with DB this way. 🙂 ❤ 🙂 [I just love programming and it's nature]
There are many things as well from SQLAlchemy core those we can talk about but I feel we should stop here otherwise I might have to shift my career from developer to writer. 😉 😀
Let's taste some code so this post will really help me in near future when I will work with much complicated DB operations those really need mind mash up. 😉
from sqlalchemy import * # don't use * in production
# if you are using Mysql look for commented code
#engine = create_engine(‘mysql+pymysql://:@localhost/mdb_final’)
engine = create_engine(‘sqlite:////home/metal-machine/Desktop/sqlalchemy_example.db’)
# creating tables, be careful with data-types 🙂
omdb_data = Table(‘positions’, metadata,
Column(‘omdb_id’, Integer, primary_key=True),
omdb_data.create() # creating tables and values
mm = omdb_data.insert() #
So above can be considered as simplest form for understanding DB writing operations using SQLAlchemy.
Making connection and updating DBs.
# make sure this DB is already created, this time we are only creating connection to read
# or insert data if we need.
bit_fine_data = create_engine('sqlite:////home/metal-machine/Desktop/sqlalchemy_example.db')
# calling all the tables in the required DB, we just have to pass table name in
# Table Class so we will be able to access,create,insert,execute from one variable.
positions_table = Table('positons',order_data_meta, autoload=True)
balance_status_table = Table('balance_status',order_data_meta, autoload=True)
account_info_table = Table('account_info',order_data_meta, autoload=True)
# inserting values in table
# executing the insert data command
How to read data from rows or columns from DB:
db = create_engine('sqlite:////home/metal-machine/Desktop/order_id.db')
metadata = MetaData(db)
# creating instance for Table-'orders'
tickers = Table('orders', metadata, autoload=True)
#selecting particular column from table 'orders'
time_stamp = tickers.select(tickers.c.timestamp)
# creating array from the data we get in the 'timestamp' column (creating array is optional #here)
timestamp_array = np.array([i for i in time_stamp.execute()])
There are much more things left for SQLAlchemy core but I believe we should stop here and look for other things as well.
Stay tuned for SQLAlchemyORM part.