PeterHarding at 01:40, 7 January 2008

2008-01-07T01:40:42Z

PeterHarding: New page: Check this out in [HTML format|http://esbinfo:8090/download/attachments/1704632/python-and-mysql.html] ... {html}

2007-12-13T04:58:18Z

New page: Check this out in [HTML format|http://esbinfo:8090/download/attachments/1704632/python-and-mysql.html] ... {html} <DIV class="layout"> <DIV id="currentSlide"></DIV> <DIV id="header"></DI...

New page
Check this out in [HTML format|http://esbinfo:8090/download/attachments/1704632/python-and-mysql.html] ...

{html}
<DIV class="layout">

<DIV id="currentSlide"></DIV>
<DIV id="header"></DIV>
<DIV id="footer">
<H1>MySQL Users Conference 2005, Santa Clara, CA</H1>
<H2>Python and MySQL</H2>
<DIV id="controls"></DIV>
</DIV>

</DIV>

<DIV class="presentation">

<DIV class="slide">
<H1>Python and MySQL</H1>
<H3>Andy Dustman</H3>
<H4>Office of Information Technology</H4>
<H4>Terry College of Business</H4>
<H4>University of Georgia</H4>
</DIV>
<DIV class="handout">
<DL>
<DT>Andy Dustman</DT>
<DD><A href="mailto:adustman%40terry.uga.edu"></A><ADUSTMAN><A href="mailto:adustman%40terry.uga.edu"></A></ADUSTMAN><BR></BR></DD>
<DT>Terry College of Business</DT>
<DD><A href="http://www.terry.uga.edu/">http://www.terry.uga.edu/</A></DD>
<DT>University of Georgia</DT>
<DD><A href="http://www.uga.edu/">http://www.uga.edu/</A></DD>
</DL>
</DIV>

<DIV class="slide">
<H1>Python for the PyCurious</H1>
<UL>
<LI>interpreted (byte-code compiler)</LI>
<LI>interactive (easy to test ideas)</LI>
<LI>object-oriented (everything's an object)</LI>
<LI>rapid development (5-10x C++, Java)</LI>
<LI>fits your brain <I>[Bruce Eckel]</I></LI>
<LI>fits your wallet: free (OSI and GPL)</LI>
<LI>fun!</LI>
</UL>
<DIV class="handout">
<DL>
<DT>Introductory Material on Python:</DT>
<DD><A href="http://www.python.org/doc/Intros.html">http://www.python.org/doc/Intros.html</A></DD></DL>
</DIV>
</DIV>

<DIV class="slide">
<H1>Types</H1>
<DIV id="typecomparo">
<TABLE>
<THEAD>
<TR>
<TH> <BR></BR></TH><TH scope="col">Mutable</TH> <TH scope="col">Immutable</TH></TR></THEAD>
<TBODY>
<TR><TH scope="row">Sequence</TH><TD>list</TD> <TD>tuple<BR></BR>str, unicode</TD></TR>
<TR><TH scope="row">Number</TH><TD> <BR></BR></TD> <TD>int, long, float</TD></TR>
<TR><TH scope="row">Mapping</TH><TD>dict</TD> <TD> <BR></BR></TD></TR>
<TR><TH scope="row">Other</TH><TD>object</TD> <TD> <BR></BR></TD></TR>
</TBODY>
<CAPTION>The basic Python types and their mutability</CAPTION>
</TABLE>
</DIV>
</DIV>

<DIV class="slide">
<H1>Basic type examples</H1>
<PRE class="interactive">>>> i=1 # an int<BR></BR>>>> j=2**64-1 # a long integer<BR></BR>>>> print j<BR></BR>18446744073709551615<BR></BR>>>> f=3.14 # float (C double)<BR></BR>>>> c=1-1j # complex (1j is imaginary)<BR></BR>>>> print c<BR></BR>(1-1j)<BR></BR>>>> s="welcome to python!"<BR></BR>>>> s.capitalize().split() # returns a list<BR></BR>['Welcome', 'to', 'python!']<BR></BR>>>> [ word.capitalize() for word in s.split() ]<BR></BR>['Welcome', 'To', 'Python!']<BR></BR>>>> a, b = 1, 2<BR></BR>>>> print (a,b) # a tuple<BR></BR>(1, 2)<BR></BR>>>> a, b = b, a<BR></BR>>>> print (a,b)<BR></BR>(2, 1)<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Strings</H1>
<PRE class="interactive">>>> "Normal string literal isn't very interesting."<BR></BR>"Normal string literal isn't very interesting."<BR></BR>>>> 'Single quotes work "same as double".'<BR></BR>'Single quotes work "same as double".'<BR></BR>>>> """Triple-quoted strings are good for long strings<BR></BR>... which span multiple lines."""<BR></BR>'Triple-quoted strings are good for long strings\nwhich span multiple lines.'<BR></BR>>>> r"Raw strings are useful for regexs, i.e. \w+ or \1"<BR></BR>'Raw strings are useful for regexs, i.e. \\w+ or \\1'<BR></BR>>>> u"Unicode strings work just like regular strings."<BR></BR>u'Unicode strings work just like regular strings.'<BR></BR>>>> u"\u72c2\n\u7009".encode('utf-8')<BR></BR>'\xe7\x8b\x82\n\xe7\x80\x89'<BR></BR>>>> print u"\u72c2\n\u7009".encode('utf-8')<BR></BR>¿<BR></BR>¿<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Strings</H1>
<P>Lots of string methods and operators:</P>
<PRE class="interactive">>>> "Split words into a list.".split()<BR></BR>['Split', 'words', 'into', 'a', 'list.']<BR></BR>>>> ' '.join(['Join', 'a', 'list', 'of', 'strings'])<BR></BR>'Join a list of strings'<BR></BR>>>> "Concatenate" + " " + "strings"<BR></BR>'Concatenate strings'<BR></BR>>>> "Multiplicity! " * 3<BR></BR>'Multiplicity! Multiplicity! Multiplicity! '<BR></BR>>>> "Parameter %s" % "substitution"<BR></BR>'Parameter substitution'<BR></BR>>>> d = dict(first_name="Vee", last_name="McMillen",<BR></BR>... company="O'Reilly")<BR></BR>>>> "Hello, %(first_name)s. How are things at %(company)s?" % d<BR></BR>"Hello, Vee. How are things at O'Reilly?"<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Dictionaries</H1>
<P>Python dictionaries are like perl hashes:</P>
<PRE class="interactive">>>> d1={}<BR></BR>>>> d1['a']=1<BR></BR>>>> d1['b']=2<BR></BR>>>> d1['c']=3<BR></BR>>>> d1<BR></BR>{'a': 1, 'c': 3, 'b': 2}<BR></BR>>>> d2={'a': 1, 'c': 3, 'b': 2}<BR></BR>>>> d3=dict([('a',1),('b',2),('c',3)])<BR></BR>>>> d4=dict(a=1, b=2, c=3)<BR></BR>>>> d1 == d2 == d3 == d4<BR></BR>True<BR></BR>>>> len(d1)<BR></BR>3<BR></BR></PRE>
<P>Values can be any type, but keys must be immutable.</P>
</DIV>

<DIV class="slide">
<H1>Sequences</H1>
<PRE class="interactive">>>> l = ['a','b','c','d','e']<BR></BR>>>> print l[0]<BR></BR>a<BR></BR>>>> print l[-1]<BR></BR>e<BR></BR>>>> print l[2:4]<BR></BR>['c', 'd']<BR></BR>>>> s='abcde'<BR></BR>>>> print s[2:4]<BR></BR>cd<BR></BR>>>> print s[::2]<BR></BR>ace<BR></BR>>>> print s[::-1]<BR></BR>edcba<BR></BR>>>> l.append(s)<BR></BR>>>> print l<BR></BR>['a', 'b', 'c', 'd', 'e', 'abcde']<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Iterators</H1>
<UL>
<LI><CODE>iter(object)</CODE> returns an iterator object</LI>
<LI><CODE>iterobj.next()</CODE> returns the next object</LI>
<LI><CODE>StopIteration</CODE> is raised when there are no more objects
<PRE class="interactive">>>> # no normal person would do this<BR></BR>>>> l = [1, 2, 3]<BR></BR>>>> i = iter(l)<BR></BR>>>> i.next()<BR></BR>1<BR></BR>>>> i.next()<BR></BR>2<BR></BR>>>> i.next()<BR></BR>3<BR></BR>>>> i.next()<BR></BR>Traceback (most recent call last):<BR></BR> File "<STDIN>", line 1, in ?<BR></BR>StopIteration<BR></BR></STDIN></PRE></LI>
</UL>

</DIV>

<DIV class="slide">
<H1>Common iterator usage</H1>
<PRE class="interactive">>>> l = [1, 2, 3]<BR></BR>>>> for item in l:<BR></BR>... print item<BR></BR>...<BR></BR>1<BR></BR>2<BR></BR>3<BR></BR>>>> d = dict(a=1, b=2, c=3)<BR></BR>>>> for key in d:<BR></BR>... print key, d[key]<BR></BR>...<BR></BR>a 1<BR></BR>c 3<BR></BR>b 2<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Exceptions</H1>
<PRE>f = open("myfile", 'r')<BR></BR>try:<BR></BR> try:<BR></BR> for line in f:<BR></BR> try:<BR></BR> process(line)<BR></BR> except TypeError:<BR></BR> line = mangle(line)<BR></BR> try:<BR></BR> process(line)<BR></BR> except TypeError:<BR></BR> raise FoobarError, line<BR></BR> except IOError, message:<BR></BR> print "Error reading:", message<BR></BR> except FoobarError:<BR></BR> print "This file is totally munged."<BR></BR> except:<BR></BR> print "Something inexplicable happened:"<BR></BR> raise # re-raise original exception<BR></BR>finally:<BR></BR> f.close()<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Odds and ends</H1>
<UL>
<LI>Code blocks are delimited by indentation
<UL>
<LI>You probably do this already</LI>
<LI>Space or tabs, your call; just be consistent</LI>
<LI>No need for curly braces</LI>
<LI>Less cluttered, easier to read</LI>
</UL></LI>
<LI>End-of-line is a statement separator (so is <CODE>;</CODE>)</LI>
<LI>No type enforcement<UL>
<LI>Argument types are not checked</LI>
<LI>Function return types are not checked</LI>
<LI>Type checking makes your code less flexible</LI>
<LI>If you still want it, you can add it cleanly with decorators</LI></UL>
</LI><LI>Operator overloading for user-defined classes</LI>
<LI><I>Everything</I> is a reference (pass by reference)</LI>
<LI><CODE>None</CODE> object for null/missing values (equivalent to <CODE>NULL</CODE>)</LI>
</UL>
</DIV>

<DIV class="slide">
<H1>Odds and ends</H1>
<UL>
<LI>Member access with <CODE>.</CODE> operator<UL>
<LI><CODE>instance.method()</CODE></LI>
<LI><CODE>instance.attribute</CODE></LI>
<LI><CODE>instance.attribute.another</CODE></LI></UL>
</LI><LI>Functions/methods are not the only things that are callable</LI>
<LI>Decorators apply a callable to a function at creation time:<PRE>@g<BR></BR>def f(x):<BR></BR> ...<BR></BR></PRE> is equivalent to:<PRE>def f(x):<BR></BR> ...<BR></BR>f = g(f)</PRE></LI>
</UL>
</DIV>

<DIV class="slide">
<H1>The Python DB-API</H1>
<UL>
<LI>Standard API for database access</LI>
<LI>PEP 249: <A href="http://www.python.org/peps/pep-0249.html" title="PEP 249: Python Database API v2.0">http://www.python.org/peps/pep-0249.html</A></LI>
<LI>By convention, module name ends with "db", i.e. MySQLdb</LI>
<UL>
<LI>Module Interface</LI>
<LI>Connection Objects</LI>
<LI>Cursor Objects</LI>
<LI>DBI Helper Objects</LI>
<LI>Type Objects and Constructors</LI>
<LI>Implementation Hints</LI>
<LI>Major Changes from 1.0 to 2.0</LI>
</UL>
</UL></DIV>

<DIV class="slide">
<H1>Module Interface</H1>
<DL>
<DT>connect(...)</DT>
<DD>Constructor for creating a connection to the database.
Returns a Connection Object.</DD>
<DT>apilevel</DT>
<DD>String constant stating the supported DB API level.</DD>
<DT>threadsafety</DT>
<DD> Integer constant stating the level of thread safety the
interface supports.</DD>
</DL>
</DIV>

<DIV class="slide">
<H1>SQL parameter placeholders</H1>
<DT>paramstyle</DT>
<DD>String constant stating the type of parameter marker formatting expected by the interface.
<DL>
<DT>'qmark'</DT>
<DD>Question mark style, e.g. '...WHERE name=?'</DD>
<DT>'numeric'</DT>
<DD>Numeric, positional style, e.g. '...WHERE name=:1'</DD>
<DT>'named'</DT>
<DD>Named style, e.g. '...WHERE name=:name'</DD>
<DT>'format'</DT>
<DD>ANSI C printf format codes, e.g. '...WHERE name=%s'</DD>
<DT>'pyformat'</DT>
<DD>Python extended format codes, e.g. '...WHERE name=%(name)s'</DD>

MySQLdb 1.0 and 1.2 uses format and pyformat; 2.0 may also support qmark.
</DL>
</DD></DIV>

<DIV class="slide">
<H1>Exceptions</H1>
<UL>
<LI>StandardError
<UL>
<LI>Warning</LI>
<LI>Error
<UL>
<LI>InterfaceError</LI>
<LI>DatabaseError</LI>
<LI>DataError</LI>
<LI>OperationalError</LI>
<LI>IntegrityError</LI>
<LI>InternalError</LI>
<LI>ProgrammingError</LI>
<LI>NotSupportedError</LI></UL>
</LI></UL>
</LI>
</UL>
</DIV>

<DIV class="slide">
<H1>Connection Object</H1>
<DL>
<DT>.close()</DT>
<DD>Close the connection now</DD>
<DT>.commit()</DT>
<DD>Commit any pending transaction to the database. Auto-commit off by default.</DD>
<DT>.rollback()</DT>
<DD>Rollback any pending transaction.</DD>
<DT>.cursor()</DT>
<DD>Return a new Cursor Object using the connection.</DD>
<DT><I>exceptions</I></DT>
<DD>The standard exception classes; simplfies error handling in some cases</DD>
<DT>.messages</DT>
<DD>list of error/warning messages since last method call</DD>
</DL>
</DIV>

<DIV class="slide">
<H1>Cursor Object</H1>
<DL>
<DT>.description</DT>
<DD>A sequence of sequences, each of which describe a column of the result.</DD>
<DT>.rowcount</DT>
<DD>Number of rows affected by last query.</DD>
<DT>.callproc(procname[,parameters])</DT>
<DD>Call a stored database procedure with the given name.</DD>
<DT>.close()</DT>
<DD>Close the cursor now.</DD>
<DT>.execute(operation[,parameters])</DT>
<DD>Prepare and execute a database operation (query or
command). Parameters: sequence or mapping.</DD>
<DT>.executemany(operation,seq_of_parameters)</DT>
<DD>Prepare a database operation (query or command) and then
execute it against a sequence of parameters.</DD>
</DL>
</DIV>

<DIV class="slide">
<H1>Cursor Object</H1>
<DL>
<DT>.fetchone()</DT>
<DD>Fetch the next row of the result set
as a sequence, or <I>None</I> if there are no more rows.
</DD>
<DT>.fetchmany(<I>[size=cursor.arraysize]</I>)</DT>
<DD>Fetch a sequence of up to <I>size</I> rows;
may be fewer. Zero length sequence indicates end of result set.
</DD>
<DT>.fetchall()</DT>
<DD>Fetch all remaining rows as a sequence of rows.</DD>
<DT>.nextset()</DT>
<DD>Skip to the next result set. Returns a true value if there is another
result set; <I>None</I> (false) if not.</DD>
<DT>.arraysize</DT>
<DD>Default number of rows to return with cursor.fetchmany().
Default: 1.</DD>
</DL>
</DIV>

<DIV class="slide">
<H1>Cursor Object</H1>
<DL>
<DT>.rownumber</DT>
<DD>Current index into result set</DD>
<DT>.connection</DT>
<DD>The Connection object for this cursor</DD>
<DT>.scroll(value<I>[,mode='relative']</I>)</DT>
<DD>Scroll to a new position in the result set (relative or absolute).</DD>
<DT>.messages</DT>
<DD>List containing warning/error messages since last method call
(except the .fetch<I>XXX</I>() methods).</DD>
<DT>.next()</DT>
<DD>Fetches one row (like fetchone()) or raises <CODE>StopIteration</CODE>
if no rows left. <I>Iterator protocol</I></DD>
<DT>.lastrowid</DT>
<DD>Row id of the last affected row (i.e. inserting <CODE>AUTO_INCREMENT</CODE>
columns)</DD>
</DL>
</DIV>

<DIV class="slide">
<H1>MySQL for Python</H1>
<UL>
<LI>MySQL-python project on SourceForge:
<A href="http://sourceforge.net/projects/mysql-python" title="MySQL-python project on SourceForge">http://sourceforge.net/projects/mysql-python</A></LI>
<LI>Current best version: 1.2.0<UL>
<LI>Python-2.3 and newer (and maybe 2.2)</LI>
<LI>MySQL-3.23, 4.0, and 4.1 (and maybe 5.0)</LI>
<LI>Prepared statements not supported yet</LI></UL></LI>
<LI>Older version: 1.0.1<UL>
<LI>Python-1.5.2 (very old) and newer</LI>
<LI>MySQL-3.22, 3.23, and 4.0 (not 4.1 or newer)</LI>
<LI>Don't use if you can use 1.2.0</LI></UL></LI>
<LI>Vaporware version: 2.0<UL>
<LI>Python-2.4 and newer</LI>
<LI>MySQL-4.0, 4.1, and 5.0</LI>
<LI>Prepared statements will be supported</LI>
<LI>Return all text columns as <CODE>unicode</CODE> by default</LI></UL></LI>
</UL>
</DIV>

<DIV class="slide">
<H1>Architecture</H1>
<DIV class="twocol">
<H2><CODE>_mysql</CODE></H2>
<UL>
<LI>C extension module</LI>
<LI>transliteration of MySQL C API into Python objects</LI>
<LI>If you use the C API, this should be very familiar</LI>
<LI>Deprecated API calls not implemented</LI>
<LI>Not everything (particularly fields) is exposed</LI>
<LI>SQL column type to Python type conversions handled by
a dictionary</LI>
</UL>
</DIV>
<DIV class="twocol">
<H2><CODE>MySQLdb</CODE></H2>
<UL>
<LI>Adapts <CODE>_mysql</CODE> to DB-API</LI>
<LI>Many non-standard C API calls are exposed</LI>
<LI>Relatively light-weight wrapper</LI>
<LI>Implements cursors</LI>
<LI>Defines default type mappings; easily
customizable</LI>
</UL>
</DIV>
</DIV>

<DIV class="slide">
<H1>Opening a connection</H1>
<P><CODE>connect()</CODE> takes the same options as <CODE>mysql_real_connect()</CODE>, and then some.</P>
<PRE>import MySQLdb<BR></BR><BR></BR># These are all equivalent, for the most part<BR></BR>db = MySQLdb.connect("myhost", "myuser", "mysecret", "mydb")<BR></BR>db = MySQLdb.connect(host="myhost", user="myuser",<BR></BR> passwd="mysecret", db="mydb")<BR></BR>auth = dict(user="myuser", passwd="mysecret")<BR></BR>db = MySQLdb.connect("myhost", db="mydb", **auth)<BR></BR>db = MySQLdb.connect(read_default_file="/etc/mysql/myapp.cnf")<BR></BR></PRE>
<UL>
<LI><CODE>compress=1</CODE> enables gzip compression</LI>
<LI><CODE>use_unicode=1</CODE> returns text-like columns as <CODE>unicode</CODE> objects</LI>
<LI><CODE>ssl=dict(...)</CODE> negotiates SSL/TLS</LI>
</UL>
</DIV>

<DIV class="slide">
<H1>Simple query example</H1>
<PRE>import MySQLdb<BR></BR><BR></BR>db = MySQLdb.connect(read_default_file="/etc/mysql/myapp.cnf")<BR></BR>c = db.cursor()<BR></BR>c.execute("""SELECT * FROM users WHERE userid=%s""", ('monty',))<BR></BR>print c.fetchone()<BR></BR></PRE>
<H2>Notes</H2>
<UL>
<LI><CODE>('monty',)</CODE> is a 1-tuple; comma required to distinquish
from algebraic grouping</LI>
<LI>Good reasons not to use <CODE></CODE><UL>
<LI>How many columns are being returned?</LI>
<LI>What is the order of the columns?</LI></UL></LI>
<LI>Good reasons to use <CODE></CODE><UL>
<LI>Table/database browser</LI>
<LI>Lazy</LI></UL></LI></UL>
</DIV>

<DIV class="slide">
<H1>Multi-row query example</H1>
<PRE>c = db.cursor()<BR></BR>c.execute("""SELECT userid, first_name, last_name, company<BR></BR>FROM users WHERE status=%s and expire>%s""",<BR></BR>(status, today))<BR></BR>users = c.fetchall()<BR></BR></PRE>
<H2>Notes</H2>
<UL>
<LI>We know what the columns are</LI>
<LI>Could use some object abstraction</LI>
</UL>
</DIV>

<DIV class="slide">
<H1>A simple User class</H1>
<PRE>class User(object):<BR></BR><BR></BR> """A simple User class"""<BR></BR><BR></BR> def init(self, userid,<BR></BR> first_name=None, last_name=None,<BR></BR> company=None):<BR></BR> self.userid = userid<BR></BR> self.first_name = first_name<BR></BR> self.last_name = last_name<BR></BR> self.company = company<BR></BR><BR></BR> def announce(self):<BR></BR> """Announce User to the world."""<BR></BR> name = "%s %s" % (self.first_name, self.last_name)<BR></BR> if self.company:<BR></BR> return "%s of %s" % (name, self.company)<BR></BR> else:<BR></BR> return name<BR></BR><BR></BR> def str(self):<BR></BR> return self.announce()<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Multi-row query with User object</H1>
<PRE>users = []<BR></BR>c = db.cursor()<BR></BR>c.execute("""SELECT userid, first_name, last_name, company<BR></BR>FROM users WHERE status=%s and expire>%s""",<BR></BR>(status, today))<BR></BR><BR></BR>for userid, first_name, last_name, company in c.fetchall():<BR></BR> u = User(userid, first_name, last_name, company)<BR></BR> print u<BR></BR> users.append(u)<BR></BR></PRE>
<P>might produce output like:
</P><PRE>Tim O'Reilly of O'Reilly Media, Inc.<BR></BR>Monty Widenius of MySQL AB<BR></BR>Carleton Fiorina<BR></BR>Guido van Rossum of Elemental Security<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Cursors are iterators</H1>
<P>Not necessary to use <CODE>c.fetchall()</CODE>
</P><PRE>users = []<BR></BR>c = db.cursor()<BR></BR>c.execute("""SELECT userid, first_name, last_name, company<BR></BR>FROM users WHERE status=%s and expire>%s""",<BR></BR>(status, today))<BR></BR><BR></BR>for userid, first_name, last_name, company in c:<BR></BR> u = User(userid, first_name, last_name, company)<BR></BR> print u<BR></BR> users.append(u)<BR></BR></PRE>
<P>Under certain conditions, this is more efficient
than <CODE>fetchall()</CODE>, and no worse.</P>
</DIV>

<DIV class="slide">
<H1>Dictionaries as parameters</H1>
<P>Python classes typically store attributes in <CODE>dict</CODE>,
so you can get away with this:
</P><PRE>u = User(...)<BR></BR>c = db.cursor()<BR></BR>c.execute("""INSERT INTO users<BR></BR>(userid, first_name, last_name, company)<BR></BR>VALUES (%(userid)s, %(first_name)s,<BR></BR>%(last_name)s, %(company)s)""", u.dict)<BR></BR>db.commit()<BR></BR></PRE>
</DIV>

<DIV class="slide">
<H1>Multi-row INSERT</H1>
<PRE># users is a list of (userid, first_name, last_name, company)<BR></BR>c = db.cursor()<BR></BR>c.executemany("""INSERT INTO users<BR></BR>(userid, first_name, last_name, company)<BR></BR>VALUES (%s, %s, %s, %s)""", users)<BR></BR>db.commit()<BR></BR></PRE>
<P>In MySQLdb, this is converted internally to a multi-row INSERT,
which is reported to be 2-3 orders of magnitude faster.
Also works for REPLACE.</P>
</DIV>

<DIV class="slide">
<H1>Multi-row INSERT with dictionaries</H1>
<PRE># users is a list of Users<BR></BR>c = db.cursor()<BR></BR>c.executemany("""INSERT INTO users<BR></BR>(userid, first_name, last_name, company)<BR></BR>VALUES (%(userid)s, %(first_name)s,<BR></BR>%(last_names, %(company)s)""",<BR></BR>[ u.dict for u in users ])<BR></BR>db.commit()<BR></BR></PRE>
<P>This builds the parameter list with a list comprehension.</P>
</DIV>

<DIV class="slide">
<H1>Never do this</H1>
<H2>Biggest MySQLdb newbie mistake of all time:
Seeing <CODE>%s</CODE> and thinking, "I should use the <CODE>%</CODE>
operator here."</H2>
<PRE>users = []<BR></BR>c = db.cursor()<BR></BR>c.execute("""SELECT userid, first_name, last_name, company<BR></BR>FROM users WHERE status='%s' and expire>'%s'""" %<BR></BR>(status, today))<BR></BR><BR></BR>for userid, first_name, last_name, company in c:<BR></BR> u = User(userid, first_name, last_name, company)<BR></BR> print u<BR></BR> users.append(u)<BR></BR></PRE>
<P>Note use of <CODE>%</CODE> operator to insert parameter values.
This does <B>not</B> provide proper quoting (escaping of
<CODE>'</CODE>, <CODE>NULL</CODE>/<CODE>None</CODE>, or <CODE>\0</CODE>).
Passing them separately (as the second parameter)
ensures they are quoted correctly. However, <CODE>%</CODE>
<B>is</B> necessary if you have to insert arbitrary SQL
such as column or table names or <CODE>WHERE</CODE> clauses.</P>
</DIV>

<DIV class="slide">
<H1>To buffer, or not to buffer...</H1>
<DIV class="twocol">
<H2><CODE>mysql_store_result()</CODE></H2>
<LI>Stores all rows of result set in client</LI>
<LI>Large result sets can chew up a lot of memory</LI>
<LI>Size of result set known immediately</LI>
<LI>Result set is seekable</LI>
<LI>Can issue another query immediately</LI>
<LI>Used for standard MySQLdb cursor</LI>

</DIV>
<DIV class="twocol">
<H2><CODE>mysql_use_result()</CODE></H2>
<LI>Sends result set row by row</LI>
<LI>Consumes resources on server</LI>
<LI>Must fetch all rows before issuing any other queries</LI>
<LI>Size of result set unknown until finished</LI>
<LI>Not seekable</LI>
<LI>Can be used with MySQLdb's <CODE>SSCursor</CODE></LI>

</DIV>
</DIV>

<DIV class="slide">
<H1>Optional cursor classes</H1>
<P><CODE>DictCursor</CODE> causes <CODE>fetchXXX()</CODE> methods
to return mappings instead of sequences, with column names for
keys.</P>
<PRE>users = []<BR></BR>c = db.cursor(MySQLdb.cursors.DictCursor)<BR></BR>c.execute("""SELECT userid, first_name, last_name, company<BR></BR>FROM users WHERE status=%s and expire>%s""",<BR></BR>(status, today))<BR></BR><BR></BR>for row in c:<BR></BR> u = User(**row)<BR></BR> print u<BR></BR> users.append(u)<BR></BR></PRE>
<P>Note that column names happen to match <CODE>User</CODE> member names
in this case.</P>
</DIV>

<DIV class="slide">
<H1>Type objects and constructors</H1>
<UL>
<LI>Constructors<UL>
<LI>Date(year,month,day)</LI>
<LI>Time(hour,minute,second)</LI>
<LI>DateFromTicks(ticks)</LI>
<LI>TimeFromTicks(ticks)</LI>
<LI>TimestampFromTicks(ticks)</LI>
<LI>Binary(string)</LI>
</UL>
</LI><LI>Types<UL>
<LI>STRING</LI>
<LI>BINARY</LI>
<LI>NUMBER</LI>
<LI>DATETIME</LI>
<LI>ROWID</LI>
</UL>
</LI></UL>
<P>These are not often used with MySQLdb.</P>
</DIV>

<DIV class="slide">
<H1>Embedded server</H1>
<OL>
<LI>Build with embedded server support:
<PRE>$ export mysqlclient=mysqld<BR></BR>$ python setup.py build<BR></BR># python setup.py install<BR></BR></PRE>
</LI><LI><CODE>_mysql.server_start()</CODE></LI>
<LI>Use normally</LI>
<LI><CODE>_mysql.server_end()</CODE></LI>
</OL>
</DIV>

<DIV class="slide">
<H1>Questions?</H1>
<UL>
<LI>http://sourceforge.net/projects/mysql-python</LI>
<LI>http://www.terry.uga.edu/</LI>
<LI>http://www.uga.edu/</LI>
</UL>
</DIV>

</DIV><DIV class="wikisrc"></DIV><DIV class="wikisrc"></DIV><DIV class="wikisrc">{html}</DIV></DIV></DIV><P></P><P></P>

Python and MySQL - Revision history

PeterHarding at 01:02, 25 January 2008

PeterHarding at 01:40, 7 January 2008

PeterHarding at 01:39, 7 January 2008

PeterHarding at 01:39, 7 January 2008

PeterHarding at 05:30, 13 December 2007