sqlalchemy drill instructions · sqlalchemy is the python sql toolkit and object relational mapper...
TRANSCRIPT
SQLAlchemy is the Python SQL toolkit and Object
Mapper that gives application developers the full power and flexibility of SQL.
Relational
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developersthe full power and flexibility of SQL.
Contents1 We're not hiding the SQL 1
2 Database connections 1
3 Working with tables 1
4 SQL statements 2
5 Mapping tables to classes 2
6 Manipulating objects 3
7 Manipulating relations 3
8 Transactions 4
9 Advanced features 4
10 Related libraries 4
pip install sqlalchemy
1 We're not hiding the SQLSQLAlchemy is not for hiding SQL, instead it's a tool to be able to work with SQL
import logginglogging.basicConfig(level=logging.DEBUG)logging.getLogger( 'sqlalchemy.engine.base' ).setLevel( logging.DEBUG )
2 Database connectionsThe Python DB API encourages similarity between the different Python modules that are used to accessdatabases.
SQLAlchemy
engine = create_engine('sqlite:///')connection = engine.connect()result = connection.execute('select 2+2')result.scalar()
3 Working with tablesMetaData : Python objects that describe tables and other schema-level objects of a database (or multipledatabases)
Load the schema of an existing database into a MetaData object :
table = Table('person', metadata, Column( 'id', Integer, primary_key = True ), Column( 'name', String(25), nullable = False ), )
print table.columnsprint metadata.tablesmetadata.create_all(engine)
4 SQL statementsSQL statement objects can be created from a table object :
Insert statement :
clause = table.insert()print clauseengine.execute(clause, name='Guido')
Insert multiple rows at once :
clause = table.insert()print clauseengine.execute(clause, [{'name': 'Mike'}, {'name': 'Mark'}, ])
Select statement :
clause = table.select()print clauseclause = clause.limit(1)print clauseclause = clause.order_by(table.c.name)print clauseresult = engine.execute(clause)print result.fetchone()
5 Mapping tables to classesCreate a Base class :
metadata = MetaData()Base = declarative_base(metadata = metadata)
All mapped classes are subclasses from this base class :
class Person( Base ): __tablename__ = 'person' id = Column( Integer, primary_key = True ) name = Column( String(25), nullable = False )
We have now defined a class, a table and a mapper :
print Person.__table__print Person.__table__.c.keys()print Person.__mapper__
And the schema can be created :
Base.metadata.create_all(engine)
6 Manipulating objectsA session is a set of objects that will be manipulated together.
Session = sessionmaker(bind=engine)session = Session()
So, newly created objects should be added to the session :
person = Person(name='Guido')session.add(person)
And all objects in a session should be written to the database together.
print session.newsession.flush()
And retrieving objects from the database goes through the session as well.
for person in session.query(Person).all(): print person.name
7 Manipulating relationsForeign keys can be defined as well :
class Address(Base): __tablename__ = 'address' id = Column(Integer(), primary_key=True) person_id = Column(Integer(), ForeignKey(Person.id)) street = Column(String(25), nullable=False)
And the schema can be created :
Base.metadata.create_all(engine)
Using the Foreign keys, SQLAlchemy knows how to configure the relations :
Address.person = relationship(Person, backref='addresses')person = Person(name='Mark')print person.addresses
And adress objects related to persons can be created :
address = Address(person=person, street='Avenue Franklin Roosevelt')print person.addressessession.flush()
8 TransactionsTransactions and sessions work together to provide a consistent view of the data :
session.commit()person.name = 'Michael'Address(person=person, street=None)print session.dirtyprint session.newsession.flush()...session.rollback()print person.name
9 Advanced features
• Mapping arbitrary selects
• Mapping arbitrary relations
• Alternative collections
• Vertical/Horizontal partitioning
10 Related libraries
• Alembic http://pypi.python.org/pypi/alembic/
• Flask SQLAlchemy http://packages.python.org/Flask-SQLAlchemy/
• Camelot http://www.python-camelot.com/
Current Trainings Modules - Python AcademyAs of February 2014
Module Topic Length (days) in-house open
Python for Programmers 3 yes yes
Python for Non-Programmers 4 yes yes
Python for Programmers in Italian 3 yes yes
Advanced Python 3 yes yes
Introduction to Django 3 yes yes
Advanced Django 3 yes yes
Python for Scientists and Engineers 3 yes yes
Fast Code with the Cython Compiler and Fast NumPyProcessing with Cython
3 yes yes
Professional Testing with pytest and tox 3 yes yes
Twisted 3 yes yes
Plone 2 yes yes
Introduction to wxPython 2 yes yes
Introduction to PySide/PyQt 2 yes yes
SQLAlchemy 1 yes yes
High Performance XML with Python 1 yes yes
Camelot 1 yes yes
Optimizing Python Programs 1 yes yes
Python Extensions with Other Languages 1 yes no
Data Storage with Python 1 yes no
Introduction to Software Engineering with Python 1 yes no
Overview of the Python Standard Library 1 yes no
Threads and Processes in Python 1 yes no
Windows Programming with Python 1 yes no
Network Programming with Python 1 yes no
Introduction to IronPython 1 yes no
We always customize and extend training modules as needed. We offer consulting services such as codereview, custom programming and tailor-made workshops.
More Information: www.python-academy.com
Current Open Courses - Python AcademyAs of February 2014
Location Date Course Language
Leipzig,Germany
February 02 - 05, 2014 Python forNon-Programmers
English
Leipzig,Germany
February 03 - 05, 2014 Python for Programmers English
Leipzig,Germany
February 06 - 08, 2014 Python for Scientists andEngineers
English
Leipzig,Germany
February 10 - 12, 2014 Advanced Python English
Leipzig,Germany
March 20 - 21, 2014 Cython in Depth English
Montréal,Canada
April 09, 2014 Descriptors andMetaclasses Tutorial atPyCon 2014
English
Montréal,Canada
April 10, 2014 Optimization Tutorial atPyCon 2014
English
Freiburg,Germany
June 02 - 04, 2014 Professional Testing withPython
English
Leipzig,Germany
June 13, 2014 SQLAlchemy Course English
Leipzig,Germany
June 16 - 20, 2014 High-PerformanceComputing with Python
English
Leipzig,Germany
June 29 - July 02, 2014 Python fürNichtprogrammierer
German
Leipzig,Germany
June 30 - July 02, 2014 Python für Programmierer German
Leipzig,Germany
July 03 - 05, 2014 Python fürWissenschaftler undIngenieure
German
Herentals,Belgium
September 19, 2014 SQLAlchemy Course English
Herentals,Belgium
September 20, 2014 Camelot Course English
Leipzig,Germany
September 21 - 24, 2014 Python forNon-Programmers
English
Leipzig,Germany
September 22 - 24, 2014 Python for Programmers English
Leipzig,Germany
September 25 - 27, 2014 Python for Scientists andEngineers
English
Leipzig,Germany
September 29, 2014 SQLAlchemy Course English
Leipzig,Germany
September 30, 2014 Camelot Course English
Leipzig,Germany
October 16 - 17, 2014 Cython in Depth English
Herentals,Belgium
October 31, 2014 SQLAlchemy Course English
Herentals,Belgium
November 03 - 05, 2014 Python for Programmers English
Freiburg,Germany
November 24 - 26, 2014 Professional Testing withPython
English