SQL Alchemy - jordy33/turbogears_tutorial GitHub Wiki
You will learn in this lesson SQL Alchemy inside the Turbogears environment
Change the directory to the model location:
cd ~/myprojectname/myprojectname/model
Then create the file tables.py and add the following code:
from sqlalchemy import Column, Integer, Unicode
from myprojectname.model import DeclarativeBase
class Tracker(DeclarativeBase):
__tablename__ = 'tracker'
id = Column(Integer, primary_key=True)
imei = Column(Unicode(16))
ticket = Column(Integer)
name = Column(Unicode(50))
Edit the file __init__.py under the same directory:
look for the following line:
from myprojectname.model.auth import User, Group, Permission
Right below insert the following:
from myprojectname.model.tables import Tracker
- Save the file and exit.
Then change directory:
cd ~/myprojectname
Deleting the previous database: To do that go to mysql
mysql -u gpsuser -p
Inside execute the following commands:
drop database myprojectname;
create database myprojectname;
exit
Then create the new table in mysql with the following command:
gearbox setup-app -c development.ini
You should see the following output:
Running setup_app() from myprojectname.websetup
19:33:48,488 INFO [tgext.debugbar] Enabling Debug Toolbar
Creating tables
Initializing Migrations
19:33:48,725 INFO [alembic.runtime.migration] Context impl MySQLImpl.
19:33:48,725 INFO [alembic.runtime.migration] Will assume non-transactional DDL.
Enter in TG Shell:
gearbox tgshell -c production.ini
To Add a Record paste the following:
from myprojectname.model.tables import Tracker
from myprojectname.model import DBSession
import transaction
newitem=Tracker()
newitem.imei=u"0011223344556677"
newitem.ticket=57
newitem.name=u"John Doe"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
To Display all Records paste the following:
allrecords=DBSession.query(Tracker).all()
for item in allrecords:
print(item.id)
print(item.imei)
print(item.ticket)
print(item.name)
To Add another Record paste the following:
newitem=Tracker()
newitem.imei=u"9988776655443322"
newitem.ticket=22
newitem.name=u"Jane Doe"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
Display again using the above code.
To Finding one element using filter_by paste the following
query=DBSession.query(Tracker).filter_by(imei=u"0011223344556677").first()
if query is not None:
print(query.id)
print(query.imei)
print(query.ticket)
print(query.name)
To Finding all the elements using filter_by paste the following:
query=DBSession.query(Tracker).filter_by(imei=u"0011223344556677").all()
for item in query:
print(item.id)
print(item.imei)
print(item.ticket)
print(item.name)
To Query specifics AND using filter_by paste the following:
query=DBSession.query(Tracker).filter_by(imei=u"0011223344556677",id=0).all()
for item in query:
print(item.id)
print(item.imei)
print(item.ticket)
print(item.name)
To Query specifics OR using filter paste the following:
from sqlalchemy import or_
query=DBSession.query(Tracker).filter(or_(Tracker.imei==u"0011223344556677",Tracker.imei==u"9988776655443322"))
for item in query:
print(item.id)
print(item.imei)
print(item.ticket)
print(item.name)
To Query specifics AND OR (using filter_by and filter) paste the following:
from sqlalchemy import and_
query=DBSession.query(Tracker).filter_by(ticket=57).filter(and_(Tracker.imei==u"0011223344556677",Tracker.imei==u"9988776655443322"))
for item in query:
print(item.id)
print(item.imei)
print(item.ticket)
print(item.name)
To Filter Equals paste the following
items=DBSession.query(Tracker).filter(Tracker.id == 2)
for row in items:
print(row.id)
- Note: The usual operator used is == and it applies the criteria to check equality.
To Filter not Equals paste the following:
items=DBSession.query(Tracker).filter(Tracker.id != 2)
for row in items:
print(row.id)
to Filter Like paste the following:
like() method itself produces the LIKE criteria for WHERE clause in the SELECT expression.
items=DBSession.query(Tracker).filter(Tracker.name.like('Jo%'))
for row in items:
print(row.name)
To Filter IN paste the following:
This operator checks whether the column value belongs to a collection of items in a list. It is provided by in_() method.
items=DBSession.query(Tracker).filter(Tracker.id.in_([1,2]))
for row in items:
print(row.id)
To Delete one Record paste the following:
item=DBSession.query(Tracker).filter_by(imei=u"0011223344556677").first()
if item is not None:
DBSession.delete(item)
DBSession.flush()
transaction.commit()
print("Deleted")
To Update Records paste the following:
item=DBSession.query(Tracker).filter_by(imei=u"9988776655443322").first()
item.name=u"Pedro"
DBSession.flush()
transaction.commit()
To Delete all Records paste the following:
item=DBSession.query(Tracker).delete()
DBSession.flush()
transaction.commit()
Relation one to Many
In systems analysis, a one-to-many relationship is a type of cardinality that refers to the relationship between two entities (see also entity–relationship model) A and B in which an element of A may be linked to many elements of B, but a member of B is linked to only one element of A. For instance, think of A as mothers, and B as children. A mother can have several children, but a child can have only one biological mother.
In a relational database, a one-to-many relationship exists when one row in table A may be linked with many rows in table B, but one row in table B is linked to only one row in table A. It is important to note that a one-to-many relationship is not a property of the data, but rather of the relationship itself. A list of authors and their books may happen to describe books with only one author, in which case one row of the books table will refer to only one row of the authors table, but the relationship itself is not one-to-many, because books may have more than one author, forming a many-to-many relationship.
The opposite of one-to-many is many-to-one.
Go to mysql and drop database and create a new one Edit tables.py and change the tables with the following:
from sqlalchemy.orm import relation,backref,relationship
from sqlalchemy import ForeignKey
class PhoneBook(DeclarativeBase):
__tablename__ = 'phonebook'
id = Column(Integer, primary_key=True)
name = Column(Unicode(30))
birthday = Column(DateTime, default=datetime.now)
age = Column(Integer)
phone = Column(Unicode(20))
loans = relationship('Loans',cascade="all,delete", backref='phonebook')
class Loans(DeclarativeBase):
__tablename__ = 'loans'
id = Column(Integer, primary_key=True)
amount = Column(Numeric(8,2),default=0)
due_date = Column(DateTime, default=datetime.now)
phonebook_id = Column(Integer, ForeignKey('phonebook.id'))
create tables with:
gearbox setup-app -c production.ini
run shell
gearbox tgshell -c production.ini
Run the following commands
from sqlalchemy import Column, Integer, Unicode, DateTime, Numeric
from myprojectname.model import DeclarativeBase
from sqlalchemy.orm import relation,backref,relationship
from sqlalchemy import ForeignKey
import datetime
from myprojectname.model.tables import Loans,Phonebook
nl=Loans()
nl.amount=500
nl.due_date=datetime.datetime.now()
np=PhoneBook()
np.name=u"John"
np.birthday=datetime.datetime.now()
np.age=18
np.phone=u"525518686756"
np.loans.append(nl)
DBSession.add(nl)
DBSession.add(np)
DBSession.flush()
transaction.commit()
Appending another loan to the same person
query=DBSession.query(PhoneBook).filter_by(name=u"John").first()
if query is not None:
nl=Loans()
nl.amount=700
nl.due_date=datetime.datetime.now()
query.loans.append(nl)
DBSession.add(nl)
DBSession.flush()
transaction.commit()
Displaying items in query:
query=DBSession.query(PhoneBook).filter_by(name=u"John").first()
for item in query.loans:
print(item.amount)
print(item.due_date)
Appending another
np=PhoneBook()
np.name=u"Jane"
np.birthday=datetime.datetime.now()
np.age=20
np.phone=u"525555688722"
DBSession.add(np)
DBSession.flush()
transaction.commit()
Adding to Jane a loan
query=DBSession.query(PhoneBook).filter_by(name=u"Jane").first()
if query is not None:
nl=Loans()
nl.amount=50
nl.due_date=datetime.datetime.now()
query.loans.append(nl)
DBSession.add(nl)
DBSession.flush()
transaction.commit()
Displaying items in query:
query=DBSession.query(PhoneBook).filter_by(name=u"Jane").first()
for item in query.loans:
print(item.amount)
print(item.due_date)
Relation Many to Many
In systems analysis, a many-to-many relationship is a type of cardinality that refers to the relationship between two entities[1] A and B in which A may contain a parent instance for which there are many children in B and vice versa.
For example, think of A as Students, and B as Courses at a university. An Studend can have several Courses, and a Course can be taken by several Students.
In a relational database management system, such relationships are usually implemented by means of an associative table (also known as cross-reference table), say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).
Edit tables.py
Insert the following
# This is the association table for the many-to-many relationship between
# Students and Course - this is, the memberships.
from sqlalchemy import Table, Column
from myprojectname.model import metadata
student_course_table = Table('student_course', metadata,
Column('student_id', Integer,
ForeignKey('student.student_id',
onupdate="CASCADE",
ondelete="CASCADE"),
primary_key=True),
Column('course_id', Integer,
ForeignKey('course.course_id',
onupdate="CASCADE",
ondelete="CASCADE"),
primary_key=True))
class Student(DeclarativeBase):
__tablename__ = 'student'
student_id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(30))
lastname = Column(Unicode(30))
class Course(DeclarativeBase):
__tablename__ = 'course'
course_id = Column(Integer, autoincrement=True, primary_key=True)
code = Column(Unicode(10))
name = Column(Unicode(30))
students = relation('Student', secondary=student_course_table, backref='courses')
Create the tables:
gearbox setup-app -c development.ini
Running the shell
gearbox tgshell -c production.ini
Create Courses
from myprojectname.model.tables import Student,Course
from myprojectname.model import DBSession
import transaction
newitem=Course()
newitem.code="ne01"
newitem.name="Neural Networks"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
newitem=Course()
newitem.code="ece4550"
newitem.name="Control System Design"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
newitem=Course()
newitem.code="inf82"
newitem.name="IT Security"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
newitem=Course()
newitem.code="wd203"
newitem.name="Web Development"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
newitem=Course()
newitem.code="net201"
newitem.name=".Net Programming"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
Create Students
newitem=Student()
newitem.name="John"
newitem.lastname="Doe"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
newitem=Student()
newitem.name="Jane"
newitem.lastname="Smith"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
Including John to Neural Networks course
current_student=DBSession.query(Student).filter_by(name=u"John").first()
current_course=DBSession.query(Course).filter_by(code=u"ne01").first()
current_student.courses.append(current_course)
DBSession.flush()
transaction.commit()
Including John to the IT Security course
current_student=DBSession.query(Student).filter_by(name=u"John").first()
current_course=DBSession.query(Course).filter_by(code=u"inf82").first()
current_student.courses.append(current_course)
DBSession.flush()
transaction.commit()
Showing all courses from John
query=DBSession.query(Student).filter_by(name=u"John").first()
for item in query.courses:
print(item.name)
Showing all the students from the Neural Network course
query=DBSession.query(Course).filter_by(code=u"ne01").first()
for item in query.students:
print(item.name)
Including to Neural Networks course a student Jane
current_course=DBSession.query(Course).filter_by(code=u"ne01").first()
current_student=DBSession.query(Student).filter_by(name=u"Jane").first()
current_course.students.append(current_student)
DBSession.flush()
transaction.commit()
Showing all students from Neural Course
query=DBSession.query(Course).filter_by(code=u"ne01").first()
for item in query.students:
print(item.name)
Including to Control System Design course a student John
current_course=DBSession.query(Course).filter_by(code=u"ece4550").first()
current_student=DBSession.query(Student).filter_by(name=u"John").first()
current_course.students.append(current_student)
DBSession.flush()
transaction.commit()
Showing all courses from John
query=DBSession.query(Student).filter_by(name=u"John").first()
for item in query.courses:
print(item.name)
Including to Neural Networks course a student Jane
current_course=DBSession.query(Course).filter_by(code=u"ne01").first()
current_student=DBSession.query(Student).filter_by(name=u"Jane").first()
current_course.students.append(current_student)
DBSession.flush()
transaction.commit()
Showing all students from Neural
query=DBSession.query(Course).filter_by(code=u"ne01").first()
for item in query.students:
print(item.name)
Including to Control System Design course a student John
current_course=DBSession.query(Course).filter_by(code=u"ece4550").first()
current_student=DBSession.query(Student).filter_by(name=u"John").first()
current_course.students.append(current_student)
DBSession.flush()
transaction.commit()
Show all courses from John
query=DBSession.query(Student).filter_by(name=u"John").first()
for item in query.courses:
print(item.name)
Practice using the User and Group Tablles:
Open auth.py in the model folder. Find the code bellow:
user_group_table = Table('tg_user_group', metadata,
Column('user_id', Integer,
ForeignKey('tg_user.user_id',
onupdate="CASCADE",
ondelete="CASCADE"),
primary_key=True),
Column('group_id', Integer,
ForeignKey('tg_group.group_id',
onupdate="CASCADE",
ondelete="CASCADE"),
primary_key=True))
The SQLalchemy "Table" will create an associative table that will describe how the groups and users are related. In this case the relation is many users are related to many groups. Many to Many. Go to my SQL and see the associative table:
mysql> desc tg_user_group;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| group_id | int(11) | NO | PRI | NULL | |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from tg_user_group;
+---------+----------+
| user_id | group_id |
+---------+----------+
| 1 | 1 |
+---------+----------+
Here user with id 1 is related to group id 1
1 row in set (0.00 sec)
Enter in TG Shell:
gearbox tgshell -c production.ini
from myprojectname.model.auth import User,Group
from myprojectname.model import DBSession
import transaction
Lets add one group and user.
newitem=Group()
newitem.group_name=u"Coders"
newitem.display_name=u"Python Coders"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
group_query=DBSession.query(Group).filter_by(group_name=u"Coders").first()
print(unicode(group_query))
newitem=User()
newitem.user_name=u"Jorge"
newitem.email_address=u"[email protected]"
newitem.display_name=u"Jorge Macias"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
query=DBSession.query(User).filter_by(user_name=u"Jorge").first()
query._set_password("1234")
print(query._get_password())
print(query.validate_password("1234"))
print(unicode(query))
print(repr(query))
print(query.by_email_address)
The code below will add user "Jorge" to group "Coders"
query.groups.append(group_query)
DBSession.flush()
transaction.commit()
See in my sql the asssociative table:
mysql> select * from tg_user_group;
+---------+----------+
| user_id | group_id |
+---------+----------+
| 1 | 1 |
| 3 | 2 |
+---------+----------+
2 rows in set (0.00 sec)
Remove Jorge from Coders
group_query=DBSession.query(Group).filter_by(group_name=u"Coders").first()
query=DBSession.query(User).filter_by(user_name=u"Jorge").first()
query.groups.remove(group_query)
DBSession.flush()
transaction.commit()
This will add again Jorge to Coders
group_query=DBSession.query(Group).filter_by(group_name=u"Coders").first()
query=DBSession.query(User).filter_by(user_name=u"Jorge").first()
query.groups.append(group_query)
DBSession.flush()
transaction.commit()
Add Jorge to managers
group_query=DBSession.query(Group).filter_by(group_name=u"managers").first()
query=DBSession.query(User).filter_by(user_name=u"Jorge").first()
query.groups.append(group_query)
DBSession.flush()
transaction.commit()
Go to Mysql and see associative table
mysql> select * from tg_user_group;
+---------+----------+
| user_id | group_id |
+---------+----------+
| 1 | 1 |
| 3 | 1 |
| 3 | 2 |
+---------+----------+
3 rows in set (0.00 sec)
query=DBSession.query(User).filter_by(user_name=u"Jorge").first()
for item in query.groups:
print(item.group_name)
Lets add Maria to Coders group:
newitem=User()
newitem.user_name=u"Maria"
newitem.email_address=u"[email protected]"
newitem.display_name=u"Maria Lacayo"
DBSession.add(newitem)
DBSession.flush()
transaction.commit()
group_query=DBSession.query(Group).filter_by(group_name=u"Coders").first()
query=DBSession.query(User).filter_by(user_name=u"Maria").first()
query.groups.append(group_query)
DBSession.flush()
transaction.commit()
Lets print all users from Coders group:
group_query=DBSession.query(Group).filter_by(group_name=u"Coders").first()
for item in group_query.users:
print(item.user_name)
[Back]