|
|
Methods can be available to the entire module to a connection or a cursor. This section describes the various methods according to whether they are module, connection, or cursor methods.
Module methods are available to the entire module. The most important is the connect() method:
dbh = MySQLdb.connect(parameters)
Connects to the specified hostname and database with the specified username and password, returning a connection object (or database handle). The parameters include the following:
host Defaults to the localhost.
user Defaults to the current user.
passwd No default (empty password).
db No default.
conv Dictionary to map literals to the Python type. It defaults to MySQLdb.converters.conversions.
cursorclass The class that cursor() uses. It defaults to MySQLdb.cursors.Cursor.
compress Enables protocol compression.
named_pipe On Windows, connects with a named pipe.
init_command Specifies a statement for the database server to run as soon as the connection is created.
read_default_file The MySQL configuration file to use.
read_default_group The default group to read.
unix_socket On Unix, connects using the specified socket. It uses TCP by default.
port Defaults to 3306.
For example:
dbh = MySQLdb.connect(user='guru2b', passwd='g00r002b',– host='test.host.co.za', db='firstdb')
These methods are for use on a connection object, returned from the MySQLdb.connect() method. You'll almost always use the cursor() and close() methods. The commit() and rollback() methods are used only for transactions.
dbh.begin()
Begins a transaction, turning off AUTOCOMMIT if it's on, until the transaction ends with a call to commit() or rollback().
dbh.close()
Closes the connection and frees the associated resources.
dbh.commit()
Commits any open transactions.
dbh.cursor([cursorClass])
Returns a new cursor object (which provides methods to access and manipulate data). You can specify a different class, if you want (by default it's the cursorclass specified in the connection, which defaults to the Cursor class).
dbh.rollback()
Rolls back any open transactions. Closing the connection without explicitly calling this method will implicitly call rollback for any open transactions.
These methods are for accessing and manipulating data; they work on a cursor object, returned from the cursor() method.
cursor.close()
Immediately frees resources associated with the cursor.
cursor.execute(query[,parameters])
Prepares and executes a database query. The method also allows you to use placeholders to optimize repeat queries of a similar type by specifying various parameters. Placeholders are usually marked with a question mark (?), but MySQLdb does not currently support this. You need to use %s to indicate a placeholder (if the paramstyle attribute is set to format) because MySQLdb treats all values as strings no matter what type the fields actually are.
For example:
cursor.execute('INSERT INTO customer(first_name,surname) VALUES– (%s, %s)',('Mike', 'Harksen'))
You can also use a Python mapping as the second argument if you set MySQLdb.paramstyle = 'pyformat'.
You can use lists of tuples as the second parameter, but this usage is deprecated. Use executemany instead.
cursor.executemany(query,seq_of_parameters)
Prepares a database query and then runs multiple instances with placeholders to optimize repeats of similar queries.
For example:
cursor.executemany('INSERT INTO customer(first_name,surname) VALUES– (%s, %s)',(('Mike', 'Harksen'),( 'Mndeni', 'Vidal'),( 'John', 'Vilakazi')))
You can also use a Python mapping as the second set of arguments if you set MySQLdb.paramstyle = 'pyformat'.
cursor.fetchall()
Fetches all rows of a query result (from the current row pointer), returning them as a sequence of sequences (list of tuples).
The arraysize attribute of the cursor can affect the method's performance.
This throws an exception if there's an error.
For example:
cursor.execute("SELECT first_name, surname FROM customer") for row in cursor.fetchall(): print "Firstname: ", row[0] print "Surname: ", row[1]
cursor.fetchmany([size=cursor.arraysize]);
Fetches a number of rows from a query result, returning a sequence of sequences (list of tuples). You specify the number of rows with the optional size parameter or the cursor's arraysize if not specified. The method will not return more rows than are available.
It's best to use the arraysize attribute for performance reasons or to keep the size parameter the same between fetchmany calls.
This throws an exception if there's an error.
cursor.fetchone()
Returns the next row from a query result set.
This throws an exception if there's an error.
For example:
cursor.execute("SELECT first_name, surname FROM customer") row = cursor.fetchone(): print "Firstname: ", row[0] print "Surname: ", row[1]
cursor.insert_id()
A non-DB-API standard method that returns the previous AUTO_INCREMENT field value.
cursor.nextset()
These standard methods are not currently used for MySQL.
|
|