Python Data Persistence - ORM - Add Data

Python Data Persistence – ORM – Add Data

To add data in the ‘Products’ table, first initialize an object of its mapped Products class, add it to the session and commit the changes.

Example

p1 = Products(name='Laptop 1, price = 25000) 
sessionobj.add(p1) 
sessionobj.commit( )

Add above code snippets to addproducts.py. It now looks like this:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from myclasses import Products,base
engine = create_engine('sqlite:///mydb.sqlite', echo=True)
base.metadata.create_all(engine) 
from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
sessionobj = Session()
p1 = Product(name='Laptop', price=25000) 
sessionobj.add(p1) 
sessionobj.commit( )

Run the above script from the command prompt. SQLAlchemy will emit equivalent parameterized INSERT query that will be echoed on the terminal as shown below in figure 9.2:

(SQLAlchemyEnv) E:\SQLAlchemyEnv>addproducts.py 
PRAGMA table_info("Products")
( )
BEGIN (implicit)
INSERT INTO "Products" (name, price) VALUES (?, ?) ('Laptop', 25000)
COMMIT

If you want to confirm, open the database in SQLite console and view’ rows in Products table, (figure 9.3)

sqlite> .head on
sqlite> .mode column
sqlite> .open mydb.sqlite
sqlite> select * from products;
ProductID name price
———- ——- ——
1 Laptop 25000

To add multiple records at once, call the add_all() method on the session object. It requires a list of objects to be added.

Example

p2=Products(name='TV',price=40000) 
p3=Products(name=1 Router',price = 2 000) 
p4 = Products(name=1 Scanner 1,price = 5000) 
p5 = Products(name='Printer' ,price = 9000) 
p6=Products(name='Mobile',price=15000) 
sessionobj.add_all( [p2,p3,p4,p5,p6]) 
sessionobj.commit( )

Go ahead and add the ‘Customers’ class mapped to the ‘Customers’ table. Add data as per sample data given. (We shall add ‘Invoices’ class and ‘Invoices’ table a little later)

Example

class Customer(base):
table name ='Customers'
CustID=Column(Integer, primary_key=True) 
name=Column(String)
GSTIN=Column(String)

We have to add this table in the database schema by executing the following statement again:

base.metadata.create_all(engine)

Python Data Persistence – ORM – Add Data

Related