Add to Watchlist

SQLAlchemy Drill

39 views

Citation of segment
Embed Code
Purchasing a DVD Cite video

Formal Metadata

Title SQLAlchemy Drill
Title of Series EuroPython 2014
Part Number 10
Number of Parts 120
Author Janssens, Erik
License CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
DOI 10.5446/19967
Publisher EuroPython
Release Date 2014
Language English
Production Place Berlin

Content Metadata

Subject Area Computer Science
Abstract Erik Janssens - SQLAlchemy Drill If you have been looking to use SQLAlchemy in one of your projects, but found the documentation a bit overwhelming then this talk is for you. If you have used SQLAlchemy but feel there are some holes in your knowledge of the library, then this talk is for you as well. The idea is that during this talk you bring your laptop with you, and make sure you have SQLAlchemy installed. At the beginning of the talk, we fire up our Python interpreter and start to explore the library in a structured way. In the next 25 minutes, we'll go hands on through the various parts of the SQLAlchemy. We try out the concepts of each part of the library and make sure the basics are well understood. ----- In this talk will introduce the audience to SQLAlchemy in a well structured way, so that basic concepts are understood. This talk will be a combination of slides and interactive code editing in IPython. Both the working of SQLAlchemy as well as best practices in using SQLAlchemy will be demonstrated. I will demonstrate the basic workings of: * the SQL generation layer * the DDL generation * the ORM * the session * transactions The used code will allow those who have their laptop with them to try the code samples for themselves.
Keywords EuroPython Conference
EP 2014
EuroPython 2014
Series
Annotations
Transcript
Loading...
chair
of the morning everybody I think most has been used in the program you want to use this for some of in his book so that the people and not of as it were good morning my name is in a constant so I spent the last number of years working with a
sequel to me on various projects and this distort is a sequel to room so the idea is that you fire applied comes all you do that install signal can be and you followed become almost I will type on the screen you fall them on your own
console of so that at the end of this session you've actually started using sequel to me and you don't together through all the basics of volcanic the whole session can be downloaded at my website some with all the instructions if if you are not fast enough to to follow so what is take a lot is 2 things it's
a fight did that allows you to manipulate SQL and it's object-relational mapper what is the the important work on this slide indeed relational because if you look at signal and you compare it with other object-relational
mappers then you will notice that signal can be as spent a great deal of effort in getting the relational part correct so if you haven't started your Python interpreter yet do so in and let's move on if I'm going to foster please give me a sign if
I'm going to slow personal so the 1st thing you should know about signal is that it's not cool to hide SQL from you is that it's a tool that allows you to work with SQL and it will help you to manipulate SQL soul don't don't try to use it as a tool that completely hides the SQL for you but you still need to look at it and it's even good to look at it so when you work seek alternate you're encouraged to look at the SQL generates to do so you do so through the Pitons working modules so we import the loading module we're not going to call figure the loading of or interpreter
reset to them the In debugging level when this and now we'll
figure it's equal to me and to us all statements true logging module you so 1st thing at the water which is signal can Dr. engine of based if you turn on this model and you will see quantum log all the SQL it sends to the database you will also see the results received from the so this at the level of the bank the OK now if you work with databases in you work with the Python DB API and the by the DAPI is actually just the specifications to reach the various database drivers in Python conform so that all the database drivers work more or less the same way but when you work with the Python DB API you need to take care of things yourself you need to open and close connections to the database as you know
doing so doing so that uses some resources so you want to pull your connections you need a connection pool if you work with SQL statements you need to manage your process manually if you work with transactions you need to manage those as well and already at this level equal to me is going to help you just import
everything from seek alternate sequel he considered connection to in signal optimistic this is called an engine so let's create a collection so this is is a
collection of tools to an in-memory like database in memory it's handy for development because it's easy to through now we can get back up a database connection out of this so this is a collection of these engines that collect and now we have a database collection and we can some SQL true this connection same the
simplest joke among
and using a signal can be what's of it sounds student that it didn't receive anything back we got a result objects like and we call this scalar function just returns the 1st element of the 1st row and knowledge
retrieve data from the database and differences of so the engine connection pool was the 1st important concept in seeking in the 2nd important concept is made about MIT is equal to is a set objects that describe your database schema so they describe the tables your
constraints your indexes things like that so let's create a middle dot object so
this will describe a database schema and once we have this we can create a table so table and a Python inject skulls stable we give it name we specify that it belongs to this set of MIT about and
then we have some columns of the table
1st an integer primary key and I we will
give a person name must that's string trendified
character flaw and it's required field so we said noble to faults OK this is a table from the
data but table object has an attribute columns which is its
list of problems you know I printed out in the format of the OK
so you see the table as an idea and then call we can also look at the internet about that object which have a list of tables in the middle of when
not we just created our scheme of in Python objects so we we haven't yet created the tables in the database itself to do so we going to extract the net about to
create itself in the database true a collection will so the statement about that
creates all using the
engine it'll take out
collection of it needs 1 a and check beginning to full notice the SQL
creates the table is sent to the database and now the table exists in the database now I said that's equal to me to that
allows you are assisted to manipulate SQL so how do we create equals that let's create
cost for table you do so by calling the inserts methods all the table so now
we have a course like imprint across this clause is applied on
object it's applied object that represents calls the development across I get solved so the children this didn't execute so How do we execute this course again will send it through the connection to so I say and the because and I
give some argument for example name is carried out and now you
see so people can be inserted you know in our personal statements we can create a select clause In the same so table of select this is selected loss and now and this is
the nice thing about signal you can start manipulating schools so we have now a very simple rules we just selects all
persons from the database and manipulated prices I create a new clause object which the previous close to 1 print cost you see the
SQL changed the limit is there so I can further amenability schools and I can see order ordered is caused by
indeed name of the person like this print across
and the course has been manipulated as well this is very handy if you need to build up complex queries you can do much the same thing for the leading causes an update rules note let's move over to the object relational mapper part to use the object-relational mapper of signal and the easiest thing is to use the declarative extension is declarative extension allows you to define your classes
and tables at the same time using the Active Record that you can use all of that's equal to me as well but for the sake of a short control let's let's take this 1 so I'll start with a
fresh middle objects
imports declarative extension not the 1st thing we will do is create a base and all mapped losses derive from this basically schools so you create in this class using the declarative basis function in sequel argument
this will at the same time associate all the
losses that arise from the base class with a certain metabolic objects so that if we define a set of laws of the we also define tables within this method so let's define our suppliers so we have the person that's derived from base we give it and the in the table name on the on the which is the name of the table again the person needs some ID it's a primary
and we give the person's name
when this meaning of what
did I do wrong here mental state of the art for you to think you note
that once to skip Table person is already defined in this mental author chick created new this
belongs create a new method of objects from the basic
laws against and create OK
now I have this person class you'll see that at the same time we created a table if you print person and that's not stand around the table we
get a table that is the table with columns and signal to me
also created a member of the a map or object
defines how table is mapped to the class so in this case it's just easy every call maps to an attribute but that can be different as well so again you only created a
table object now we still need to create our table in the database so little statement about
read all true the engine and
it didn't actually do anything because our table existed already in the database and seek welcoming just look to see that table already exists and it doesn't create a new 1 so now we have created a
close next thing we're going to do is we're going to create objects again the relational part can be uh comes into scope now because in signal to me you never manipulate or create an object alone you always work with sets of related objects and those sets of related objects your group in a session so session is is a group of objects you're working with so we have to create a session 1st In sequel you 1st create a session factory in a session factory it's normally indicated this session with a capital S is created with the decision maker function and it's bound to a collection of tools to an engine
from the groom the 1st importance all the
object-relational not related things now I have 3
and I have a session factory with the session factory acting create a session is is usually in the documentation session with a small as so
this is our session now I can start to create a personal objects with the person the name data I have personally but this
person doesn't belong to a session so the 1st thing I have to do after creating a person is added to this set of objects and going to manipulate now I can shake for the
person is not in the session that's true and if you
look In session don't you you'll get a list or a set of all the objects that have not yet been written to the database to write the objects to the date of this kind of session of which and you
see now the SQL is generated to insert the person if you want to retrieve objects from the database you go through the secessionist so you've created a Kuwaiti objects which you do from the session coding methods to the grade the person OK now I'm
going to all persons from the database and I don't at the
end of the list of persons objects by now I said that then most important part of supporting the is mapping the relations so let's let's start with the relations we going to create an address cost which is related to a person saying we create an address closer choose a subclass of basis as
a different table give it a
primary key I give it a
streets so very simple
interest OK so again we
created or objects now we still need to create the table in the database so that
recreates more tables from the metal bar and you see a new table is created in the database so now we only
defined our relationship at the database I forgot to do this no 1 I'm going to add to
the address person and the call just an integer so
that for came to the person for group to do this in the definition but I can't as later on I'll have to recreates the database schema now on drop everything and created again
so in the in this line I only defines the relationship at the database level by and I also want to define at the object level so and if the interest cost of person at tributes which relates to a person and at the
same time I'm going to give person lost a natural you'd
addresses which is a list of all the addresses related to the 1st there's no like this means at the relation between the person and address labels see that there is a foreign key and then we'll figure out how to do the relations that the object level OK I have and I need a new person this is my OK so now I
have my personal home I'll create an address related to the person let's see you do list anecdotal Maddox knowledge just around the corner here so now I can look in I have to add the person to the secessionist and what's the what's in the session so a person is in the session and the signal can be added to be addressed in the session as well to the addressed is related to the person so we should manipulating them um together let's write everything to the database the session so
signal inserts person and addressed in the right order and what we can do now is slow we
can look at the person not interested and we see here is a list of addresses for object
so this is sort of indeed the basic functionality that signal can be office you there is also more
advanced functionalities which you can look at it in the documentation and all the important part is working with transactions so I'm not going to demonstrate that to date what else can you do you can map arbitrary select to Europe losses instead of just mapping tables to colossus that's very interesting you can also arbitrary relations and so the lonely just foreign key defines relations you can use alternative collection for your relations so in our example the list of addresses of 1st DD addresses on a person released but you can
use sets and dictionaries as well and seek a lot supports vertical and horizontal partitioning of few databases there's also a large number of related libraries that signal can be 1 of them is 11 which helps you in defining a schema migrations no you have lost the so fast signal can be integrated the lost and then you can look at the library I developed which is a graphical interface on top of the world so this concludes the sequel to material any questions please go thank you think that the things we want presentation anybody with this is is that what we did before that's equal to because a who is a sequence of I have no questions have have so so I think in my mind long-standing is significantly Christmas uh is there any plan for us the most up-to-date yeah that if you have a column that could have an excellent but fair right yeah that that's in a different light it's in the 11 beaches it's in the with along that you can do things like that like automatic schema migration automatic when it's possible and I don't really believe in automatic schema migrations but Alembic can do it you can also define your schema migration in Alembic the last 2 your database schema to the relations automatically and production yeah I have 1 question that our database and their so so using psychology excellent achievements sorry I I didn't understand them again and that of engineers are still heavily rely on time due to directly and so we have like a gap between the application developers using psychologically and thought of as engineers like during this low-level you know was technology would you say that relying just on the criteria because it has a set of really you can do for signal so it's not like as evil or and stuff but only so all of which is I can just switch totally to secure as a medium dumped up using directed psychology I would certainly consider that I I use a psycho PG optimal this will but only true signal can be an and you will see that the quality of the books supports a lot of that cycle PG stuff and the things it doesn't support it can be very easily added to it specifically for your application so if you use no some psycho PG functions that of functionality that is not supported turcica to me it you can really defined so sequel to me uses that functionality intellectually lose anything when you when you go sequel can at that's that's very important to to know that that you don't lose anything you don't lose the flexibility I have another question uh do you know any good framework for a accessing stop procedures from like probabilistic also has some abstraction low for those you know if you find 1 that you have had at it is this the because of the lack of something so I think at the moment and you my as I was looking at limit for a short while it is possible to can a connected to the models to define bean soup locally to and that's all you don't have to define and twice basically just use the Alembic Ford that that is my greatest but the model is defined in the welcoming yes you can do that in all the 2nd order what I usually do it I keep because the model continually it involves all the time of course so you defined the mother and then you keep updating that from time to time you take a snapshot of the model With Alembic and that all your database versions from other so you don't have to redefine the Basic Eiffel just use the walking defined model in the last year and that just passes on to Lambert to live together that's that's that works with the work of thank you OK if there are no further 15 favored into the speaker theconversion
Metropolitan area network
Computer programming
Code
Number
Touchscreen
Computer animation
Sequel
Lecture/Conference
Projective plane
Website
Video game console
Data type
Maxima and minima
Metropolitan area network
Inclusion map
Slide rule
Computer animation
Lecture/Conference
Network operating system
Boom (sailing)
Finitary relation
Interpreter (computing)
Object-relational mapping
Mereology
Metropolitan area network
User interface
Interactive television
File format
Density of states
Computer animation
Module (mathematics)
Interpreter (computing)
Energy level
Species
Information
Figurate number
Physical system
Data type
Database transaction
Process (computing)
User interface
Scientific modelling
Interactive television
Water vapor
Connected space
Latent heat
Computer animation
Database
Device driver
Statement (computer science)
Module (mathematics)
Quantum
Energy level
Information
Figurate number
Physical system
Resultant
Data type
Read-only memory
User interface
Sequel
Interactive television
Interface (computing)
Storage area network
Uniform resource locator
Connected space
Database
Information
Gamma function
Data type
Metropolitan area network
Software developer
Parameter (computer programming)
Mass
Port scanner
Connected space
Maxima and minima
Exterior algebra
Computer animation
String (computer science)
Remote Access Service
Physical system
Form (programming)
Inheritance (object-oriented programming)
User interface
Interactive television
Compiler
Student's t-test
Interface (computing)
Social class
Scalar field
Database
Statement (computer science)
Regular expression
Information
Subtraction
Formal grammar
Data type
Metropolitan area network
Element (mathematics)
Set (mathematics)
Table (information)
Connected space
Computer animation
Object (grammar)
Physical system
Resultant
Row (database)
Injektivität
Metropolitan area network
Meta element
Constraint (mathematics)
User interface
Ext functor
Set (mathematics)
Table (information)
Table (information)
Maxima and minima
Subject indexing
Connected space
Computer animation
Lecture/Conference
Database
Information
Energy level
Object (grammar)
Physical system
User interface
Key (cryptography)
Ext functor
Parameter (computer programming)
Interface (computing)
Term (mathematics)
Discrete element method
Table (information)
Table (information)
Word
Connected space
Social class
Type theory
Computer animation
String (computer science)
Integer
Information
Integer
Physical system
Oracle
Data type
Metropolitan area network
Software bug
User interface
File format
Electronic mailing list
Field (computer science)
Variance
Table (information)
Table (information)
Attribute grammar
Tabu search
Maxima and minima
Connected space
Computer animation
Energy level
Information
Object (grammar)
Integer
Physical system
Meta element
Metropolitan area network
Numbering scheme
Electronic mailing list
Table (information)
Table (information)
Tabu search
Connected space
Computer animation
Internetworking
Database
Information
Object (grammar)
Gamma function
Integer
Meta element
Ring (mathematics)
Table (information)
Table (information)
Tabu search
Inclusion map
Connected space
Computer animation
Lecture/Conference
Database
Statement (computer science)
Information
Key (cryptography)
Integer
Arc (geometry)
Meta element
Metropolitan area network
Software developer
Insertion loss
Angle
Binary file
Table (information)
Table (information)
Connected space
Tabu search
Computer animation
Lecture/Conference
String (computer science)
Key (cryptography)
Information
Object (grammar)
Integer
Metropolitan area network
Meta element
Cursor (computers)
Insertion loss
Parameter (computer programming)
Table (information)
Tabu search
Connected space
Computer animation
Statement (computer science)
Statistics
Key (cryptography)
Information
Integer
Computer animation
Closed set
Database
Letterpress printing
Information
Key (cryptography)
Object (grammar)
Integer
Rule of inference
Table (information)
Maxima and minima
Metropolitan area network
Limit (category theory)
Electronic mailing list
Limit (category theory)
Mereology
Rule of inference
Table (information)
Tabu search
Order (biology)
Computer animation
Query language
Order (biology)
Information
Object-relational mapping
Extension (kinesiology)
Social class
Metropolitan area network
Meta element
Game controller
Ring (mathematics)
Multiplication sign
Limit (category theory)
Table (information)
Table (information)
Order (biology)
Computer animation
Lecture/Conference
Object (grammar)
Row (database)
Metropolitan area network
Meta element
Sequel
Multiplication sign
Basis (linear algebra)
Ext functor
Attribute grammar
Insertion loss
Limit (category theory)
Parameter (computer programming)
Functional (mathematics)
Declarative programming
Order (biology)
Social class
Computer animation
Information
Normal (geometry)
Extension (kinesiology)
Associative property
Form (programming)
Social class
Metropolitan area network
Meta element
Standard deviation
Inheritance (object-oriented programming)
Physical law
Ext functor
Parameter (computer programming)
Insertion loss
Limit (category theory)
Bit rate
Set (mathematics)
Table (information)
Table (information)
Word
Order (biology)
Computer animation
Normal (geometry)
Oracle
Data type
Standard deviation
Addition
State of matter
Abstract syntax tree
Limit (category theory)
Bit rate
Table (information)
Tabu search
Word
Order (biology)
Social class
Cache (computing)
Interpreter (computing)
Drill commands
Regular expression
Data type
Metropolitan area network
Computer font
Polymorphism (materials science)
Parameter (computer programming)
Mass
Ext functor
Grand Unified Theory
Euler angles
Batch processing
Maxima and minima
Arithmetic mean
Computer animation
Revision control
Partial derivative
Data Encryption Standard
Key (cryptography)
Form (programming)
Oracle
Extension (kinesiology)
Metropolitan area network
Computer animation
Oval
Authorization
Bit rate
Object (grammar)
Existence
Table (information)
Table (information)
Metropolitan area network
Meta element
Social class
Computer animation
Oval
Multiplication sign
Drill commands
Bit rate
Existence
Table (information)
Table (information)
Social class
Metropolitan area network
Mapping
Ring (mathematics)
Bit rate
System call
Table (information)
Attribute grammar
Table (information)
Tabu search
Social class
Computer animation
Lecture/Conference
Personal digital assistant
Database
Object (grammar)
Social class
Social class
Beta function
Computer animation
Bit rate
Existence
Table (information)
Table (information)
Data type
Metropolitan area network
Theory of relativity
Sequel
Decision theory
Cloud computing
Set (mathematics)
Mereology
Functional (mathematics)
Local Group
Table (information)
Social class
Computer animation
Factory (trading post)
Object (grammar)
output
Data type
Metropolitan area network
Division (mathematics)
Cloud computing
Abstract syntax tree
Table (information)
Tabu search
Maxima and minima
Computer animation
Factory (trading post)
Key (cryptography)
Object (grammar)
output
Arc (geometry)
Data type
Metropolitan area network
System call
Electronic mailing list
Cloud computing
Ext functor
Set (mathematics)
Higher-order logic
Plane (geometry)
Computer animation
Database
Statistics
Information
Object (grammar)
output
Directed graph
Data type
Computer font
System call
Computer animation
Database
Gradient
Cloud computing
Abstract syntax tree
Information
Object (grammar)
output
Metropolitan area network
Theory of relativity
Inheritance (object-oriented programming)
Electronic mailing list
Basis (linear algebra)
Cloud computing
Mereology
Maxima and minima
Social class
Computer animation
Information
Object (grammar)
Address space
Metropolitan area network
Word
Type theory
Computer animation
Key (cryptography)
Ext functor
Information
Oracle
Table (information)
Table (information)
Maxima and minima
Data type
Metropolitan area network
Addition
Ring (mathematics)
Abstract syntax tree
Table (information)
Table (information)
Inclusion map
Type theory
Computer animation
Interpreter (computing)
Database
Data acquisition
Information
Regular expression
Object (grammar)
Remote Access Service
Oracle
Data type
Metropolitan area network
Computer animation
Database
Information
Key (cryptography)
Integer
Table (information)
Table (information)
Data type
Link (knot theory)
Mountain pass
Relational database
Ext functor
Drop (liquid)
System call
Local Group
Table (information)
Maxima and minima
Social class
Sic
Computer animation
Database
Integer
Key (cryptography)
Information
Integer
Arc (geometry)
Address space
Data type
Metropolitan area network
Drop (liquid)
Line (geometry)
Table (information)
Computer animation
Database
Energy level
Information
Key (cryptography)
Object (grammar)
Normal (geometry)
Integer
Data type
Theory of relativity
Key (cryptography)
Multiplication sign
Electronic mailing list
Port scanner
Table (information)
Independence (probability theory)
Social class
Computer animation
Energy level
Key (cryptography)
Information
Figurate number
Object (grammar)
Normal (geometry)
Gamma function
Integer
Arc (geometry)
Address space
Extension (kinesiology)
Metropolitan area network
Set (mathematics)
Mathematical singularity
Electronic mailing list
Insertion loss
Maxima and minima
Computer animation
Database
Order (biology)
Key (cryptography)
Information
Integer
Address space
Metropolitan area network
Computer animation
Electronic mailing list
Object (grammar)
Quicksort
Office suite
Functional (mathematics)
Address space
Database transaction
Presentation of a group
Sequel
Scientific modelling
Multiplication sign
Insertion loss
Mereology
Data dictionary
Number
Tabu search
Revision control
Lecture/Conference
Database
Data mining
Software framework
Arithmetic logic unit
Address space
Graphical user interface
Partition (number theory)
Metropolitan area network
Theory of relativity
Product (category theory)
Software developer
Moment (mathematics)
Electronic mailing list
Planning
Calculus
Set (mathematics)
Euler angles
Limit (category theory)
Cartesian coordinate system
Sequence
Functional (mathematics)
Table (information)
Maxima and minima
Human migration
Computer animation
Boom (sailing)
Order (biology)
Procedural programming
Cycle (graph theory)
Library (computing)
Computer animation
Loading...
Feedback

Timings

 1222 ms - page object

Version

AV-Portal 3.8.0 (dec2fe8b0ce2e718d55d6f23ab68f0b2424a1f3f)