Using SQLAlchemy in python to control database

SQLAlchemy is an open-source python module that let developers easily create and manipulate databases with python. SQL language is not required because everything is already encapsulated inside the module. It also conveniently has a “session” feature that provides a function similar to git – all changes can be staged and committed altogether.

An introductory tutorial can be found here.

The idea is called Object Relational Programming(ORM). SQLAlchemy lets you use object oriented programming language such as python to operate on databases. For example, for a Student table in a database, it can have several attributes: id, name, and school. id and name can be represented by primitive types but school may be another object. In the actual database, there is a link from the student to the school that he or she belongs to. SQLAlchemy transforms this relationship into object references, which makes it easy to update tables with python code that can be run on a server.

Here are some basic code segments that can be used:

#import and basic setup
import os
import sys
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine

Base = declarative_base()

The above piece of code import necessary modules. Column, ForeignKey etc are not default python classes, so they must be imported. A declarative_base is a class that contains a database table, a mapper which matches the table with python class, and a class object. An engine stores data in the local directory.

class Restaurant(Base):
    __tablename__ = 'restaurant'

    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)


class MenuItem(Base):
    __tablename__ = 'menu_item'

    name = Column(String(80), nullable=False)
    id = Column(Integer, primary_key=True)
    description = Column(String(250))
    price = Column(String(8))
    course = Column(String(250))
    restaurant_id = Column(Integer, ForeignKey('restaurant.id'))
    restaurant = relationship(Restaurant)

As you can see, it is very easy to create classes in python/tables in database. “__tablename__” always needs to be provided. Other elements are declared like normal classes. String(n) means this String can have as many as n characters. When nullable is true, the column can never be empty.

At the end, engine needs to be started:

engine = create_engine('sqlite:///restaurantmenu.db')

Base.metadata.create_all(engine)

After this, a restaurantmenu.db file will be created at local directory and fully functional. Hooray!

That’s pretty much it. I’ll update when I learn more. Code for this post is from Udacity.
 

14 thoughts on “Using SQLAlchemy in python to control database”

  1. 除皺特點 純度高,效果好 見效迅速,無創無痛 安全精準,表情自然 唯一在臨床有20多年的注射材料,全球超過11000000人使用 適用範圍 魚尾紋、額頭紋、眉間紋、鼻紋和頸部皺紋都可以祛除; 最適合於早期的、不太明顯的皺紋 不須使用任何的鎮靜劑或局部麻醉劑,且Botox除皺治療後可立即繼續進行正常的活動。 Botox除皺可以利用午休時間約診即可,完全不影響工作的安排。BOTOX肉毒桿菌素是一種高度純化的蛋白質,經過注射之後,能夠使導致動態皺紋的肌肉得到放鬆,它能夠阻斷導致肌肉收縮的神經細胞,使面部線條變得平滑並防止新皺紋形成。全球銷量第一品牌 在中國唯一獲得SFDA、FDA批准用於醫療美容的肉毒桿菌素 唯一一個擁有40年安全記錄的肉毒桿菌素 在70個國家獲得批准使用 BOTOX肉毒杆菌素

  2. Throughout the grand design of things you actually secure a B+ with regard to effort. Where exactly you actually misplaced us was on your details. As as the maxim goes, the devil is in the details… And that couldn’t be much more correct right here. Having said that, allow me tell you just what did do the job. Your authoring is incredibly persuasive and this is probably the reason why I am making the effort to comment. I do not make it a regular habit of doing that. Second, whilst I can notice the leaps in logic you come up with, I am not sure of how you seem to connect the points which in turn produce the conclusion. For the moment I will yield to your position but hope in the future you connect your dots much better.

  3. 瘦身絕對不用節食!愛吃女孩全靠一招兩年減掉100磅 Marie Claire (HK) Edition 很多人一提起減肥就千般不願:「因為那代表要放棄最愛吃的美食呀!」如果你這樣想,就大錯特錯了。舊時候的減肥方法離不開做運動和節食,什麼cupcake, 雪糕,甚至粥粉麵飯,想也不能想,但美國的一位愛吃女

  4. #NYFW Michael Kors藍孩兒看過來! Marie Claire (HK) Edition 今季Michael Kors最令我心心眼是多套以藍色調為主的設計,無論是牛仔褲管上的飄逸流蘇、過長的恤衫袖子、乖巧度十足的格子圓領,都令人大讚。另外我們亦看到上季流行的car-washed skirt

  5. ~100 預防高危致癌的 HPV 16、18 型號 (可減低 70 患子宮頸癌的風險) ~100 減低引致生殖器官濕疣 (俗稱「椰菜花」) 的 HPV 6、11 型的感染 (可減低超過 90 患生殖器官濕疣的風險) HPV4合1子宮頸癌疫苗 Gardasil HPV病毒會感染人類的皮膚及黏膜,一般會透過性接觸及親密的皮膚接觸而受到感染,是一種男性與女性都可能感染的常見病毒。可感染身體各個部位的HPV超過100種,當中有部份的HPV類型可影響生殖器部位,導致生殖器疣(genital warts) 、子宮頸細胞異常(abnormal cervical cells) ,甚至子宮頸癌 (cervical cancer)。 4合1 HPV 子宮頸癌疫苗,覆蓋4種高危HPV病毒:6、 11、16及18型(約70的子宮頸癌由HPV16和HPV18病毒引致),有助預防子宮頸癌、外陰癌、陰道癌及生殖器官濕疣

  6. 當蛋白線埋入皮膚後,皮下組織會將蛋白線視為異物,啟動異物反應,因此刺激膠原蛋白生長,且可促進新陳代謝,更新老化肌膚,所以客戶在術後也會發覺膚質變得較透亮白皙。膠原蛋白提拉線像一個“磁力線”一樣,將埋入處附近的肌肉和脂肪固定在原處,不會往下墜,其後會吸引皮下組織往蛋白線集中部位移動,就會逐漸產生提拉的緊實效果。也被用於填充美容的微整形,蛋白線材就好比蓋房子的鋼骨支架,先埋入皮膚當作基底結構,然後再注入好比水泥的玻尿酸或自體脂肪,因為有吸附力不易擴散,就可穩定固定住填充物,讓立體支撐力效果更好。

  7. Jordan 12 Gym Red 2018 http://www.jordan12gymred.us.com/
    nike factory outlet http://www.nikefactoryoutletstoreonline.com/
    nike factory outlet store online http://www.nikefactoryoutletstoreonline.us/
    Nike Outlet http://www.nikestores.us.com/
    retro jordan 33 http://www.jordan33.us/
    cheapjerseysfromchina http://www.cheapjerseysfromchina.us/
    cheap custom nfl jerseys http://www.customnfljerseys.us/
    jordan 11 concord 2018 http://www.jordan11concord.us.com/
    Air Jordan 12 Gym Red http://www.jordan12gymred.us/
    Red Jordan 12 http://www.redjordan12.us/
    Yeezy Shoes http://www.yeezy.com.co/
    Yeezy http://www.yeezys.us.com/
    Yeezy http://www.yeezysupply.us.com/
    Yeezy Boost 350 http://www.yeezy-shoes.us.com/
    Yeezy Boost http://www.yeezy-boost350.com/
    Yeezy Boost 350 V2 http://www.yeezyboost350.us.com/
    Yeezy Blue Tint http://www.yeezybluetint.com/
    Yeezy 500 http://www.yeezy500utilityblack.com/
    Adidas Yeezy 500 http://www.yeezy500utilityblack.us/
    Nike Air VaporMax http://www.vapor-max.org.uk/
    Salomon Shoes http://www.salomon-shoes.org.uk/
    Salomon Shoes http://www.salomons.me.uk/
    Salomon Speedcross 4 http://www.salomonspeedcross4.org.uk/
    Off White Jordan 1 http://www.offwhitejordan1.com/
    Nike Air VaporMax http://www.nikevapormax.org.uk/
    Nike Element 87 http://www.nikereactelement87.us.com/
    Nike React Element 87 http://www.nikereactelement87.us/
    Nike Vapormax Plus http://www.nikeplus.us/
    Nike Outlet Online http://www.nike–outlet.us/
    Nike Outlet http://www.nikeoutletstoreonlineshopping.us/
    Nike Outlet http://www.nikeoutletonlineshopping.us/
    NBA Jerseys http://www.nikenbajerseys.us/
    Air Max 97 http://www.nikeairmax.us/
    Nike Air Max 2017 http://www.max2017.us/
    Jordan Shoes 2018 http://www.jordan-com.com/
    Jordan 11 Concord http://www.jordan11-concord.com/
    Kanye West Yeezys Boost Shoes http://www.cs7boots1.com/
    Cheap NBA Jerseys http://www.cheapnba-jerseys.us/
    Birkenstock Sandals http://www.birkenstocksandalsuk.me.uk/
    Basketball Jersey http://www.basketball-jersey.us/
    Balenciaga UK http://www.balenciaga.me.uk/
    Balenciaga http://www.balenciagauk.org.uk/
    Balenciaga http://www.balenciagatriples.org.uk/
    Balenciaga UK http://www.birkenstocks.me.uk/
    Balenciaga Trainers http://www.balenciagatrainers.org.uk/
    Nike Air Max http://www.airmax270.org.uk/
    Yeezy http://www.adidasyeezyshoes.org.uk/
    Adidas Yeezy http://www.adidasyeezyshoes.org.uk/

Leave a Reply

Your email address will not be published. Required fields are marked *