用 Python 的输入输出功能读取和写入数据

读取、写入和 Python

在 “探索 Python” 系列以前的文章中，学习了基本的 Python 数据类型和一些容器数据类型，例如 tuple、string 和 list。其他文章讨论了 Python 语言的条件和循环特性，以及它们如何与容器数据类型进行协作来简化编程任务。编写程序的最后一个基本步骤就是从文件读取数据和把数据写入文件。阅读完这篇文章之后，可以在自己的 to-do 列表中加上检验这个技能学习效果的任务。

简单输出

贯穿整个系列，一直用 print 语句写入（输出）数据，它默认把表达式作为 string 写到屏幕上（或控制台窗口上）。清单 1 演示了这一点。清单 1 重复了第一个 Python 程序 “Hello, World!”，但是做了一些小的调整。

清单 1. 简单输出

>>> print "Hello World!"Hello World!>>> print "The total value is = $", 40.0*45.50The total value is = $ 1820.0>>> print "The total value = $%6.2f" % (40.0*45.50)The total value = $1820.00>>> myfile = file("testit.txt", 'w')>>> print >> myfile, "Hello World!">>> print >> myfile, "The total value = $%6.2f" % (40.0*45.50)>>> myfile.close()

正如这个示例演示的，用 print 语句写入数据很容易。首先，示例输出一个简单的 string。然后创建并输出复合的 string，这个字符串是用 string 格式化技术创建的。

但是，在这之后，事情发生了变化，与代码以前的版本不同。接下来的一行创建 file 对象，传递进名称 "testit.txt" 和 'w' 字符（写入文件）。然后使用修改过的 print 语句 —— 两个大于号后边跟着容纳 file 对象的变量 —— 写入相同的 string。但是这一次，数据不是在屏幕上显示。很自然的问题是：数据去哪儿了？而且，这个 file 对象是什么？

第一个问题很容易回答。请查找 testit.txt 文件，并像下面那样显示它的内容。

% more testit.txt Hello World!The total value = $1820.00

可以看到，数据被准确地写入文件，就像以前写到屏幕上一样。

现在，请注意清单 1 中的最后一行，它调用 file 对象的 close 方法。在 Python 程序中这很重要，因为在默认情况下，文件输入和输出是缓冲的；在调用 print 语句时，数据实际未被写入；相反，数据是成批写入的。让 Python 把数据写入文件的最简单方式就是显式地调用 close 方法。

文件对象

file 是与计算机上的文件进行交互的基本机制。可以用 file 对象读取数据、写入数据或把数据添加到文件，以及处理二进制或文本数据。

学习 file 对象的最简单方法就是阅读帮助，如清单 2 所示。

清单 2. 得到 file 对象的帮助

>>> help(file)Help on class file in module __builtin__:class file(object) |  file(name[, mode[, buffering]]) -> file object |   |  Open a file.  The mode can be 'r', 'w' or 'a' for reading (default), |  writing or appending.  The file will be created if it doesn't exist |  when opened for writing or appending; it will be truncated when |  opened for writing.  Add a 'b' to the mode for binary files. |  Add a '+' to the mode to allow simultaneous reading and writing. |  If the buffering argument is given, 0 means unbuffered, 1 means line |  buffered, and larger numbers specify the buffer size. |  Add a 'U' to mode to open the file for input with universal newline |  support.  Any line ending in the input file will be seen as a '/n' |  in Python.  Also, a file so opened gains the attribute 'newlines'; |  the value for this attribute is one of None (no newline read yet), |  '/r', '/n', '/r/n' or a tuple containing all the newline types seen. |   |  'U' cannot be combined with 'w' or '+' mode. |   |  Note:  open() is an alias for file(). |   |  Methods defined here:...

正如帮助工具指出的，使用 file 对象很简单。用 file 构造函数或 open 方法创建 file 对象，open 是 file 构造函数的别名。第二个参数是可选的，它指定文件的使用方式：

• 'r' （默认值）表示从文件读取数据。
• 'w' 表示要向文件写入数据，并截断以前的内容。
• 'a' 表示要向文件写入数据，但是添加到当前内容尾部。
• 'r+' 表示对文件进行读写操作（删除以前的所有数据）。
• 'r+a' 表示对文件进行读写操作（添加到当前内容尾部）。
• 'b' 表示要读写二进制数据。

这篇文章的第一个代码清单向文件写入数据。现在，清单 3 显示如何把这个数据读入 Python 程序，并解析文件的内容。

清单 3. 从文件读取数据

>>> myfile = open("testit.txt")>>> myfile.read()'Hello World!/nThe total value = $1820.00/n'>>> str = myfile.read()>>> print str>>> myfile.seek(0)>>> str = myfile.read()>>> print strHello World!The total value = $1820.00>>> str.split()['Hello', 'World!', 'The', 'total', 'value', '=', '$1820.00']>>> str.split('/n')['Hello World!', 'The total value = $1820.00', '']>>> for line in str.split('/n'):...     print line... Hello World!The total value = $1820.00>>> myfile.close()

要读取数据，首先要创建合适的 file 对象 —— 在这个示例中，文件对象打开 testit.txt 文件，并用 read 方法读取内容。这个方法把整个文件读入一个 string，然后在程序中把这个字符串输出到控制台。在对 read 方法的第二个调用中，试图把值分配给 str 变量，结果返回一个空的 string。这是因为第一个 read 操作读入了整个文件。当试图再次读取内容时，已经到了文件末尾，所以什么也读不到。

这个问题的解决方案也很简单：让 file 对象返回文件的开头。回到开头要通过 seek 方法进行，它接受一个参数，表示要从文件中的什么位置开始读取或写入（例如，0 代表文件开头）。seek 方法支持更复杂的操作，但是可能会有危险。对于目前来说，我们还坚持采用简单方式。

现在回到了文件的开始之处，可以把文件内容读入 string 变量并对 string 做适当地解析。请注意，在文件中，行之间用新行（或行结束）字符区分。如果试着在 string 上调用 split 方法，它会在空白字符（例如空格）处进行拆分。为了让方法根据新行字符拆分各行，必须显式地指定新行字符。然后可以拆分 string 并在 for 循环中对文件的行进行迭代。

看起来仅仅从文件中读取和处理一行内容都有许多工作要做。Python 要让简单的事情变容易，所以您可能想知道这个任务有没有快捷方式可用。如清单 4 所示，答案是 yes。

清单 4. 读取和解析行

>>> myfile = open("testit.txt")>>> for line in myfile.readlines():...     print line... Hello World!The total value = $1820.00>>> myfile.close()>>> for line in open("testit.txt").readlines():...     print line... Hello World!The total value = $1820.00>>> for line in open("testit.txt"):...     print line... Hello World!The total value = $1820.00

清单 4 演示了读取和解析文本文件行的三种技术。首先，打开文件并把它分配给变量。然后调用 readlines 方法，把整个文件读入内存并把内容拆分成 string 列表。for 循环在 string 列表上进行迭代，一次输出一行。

第二个 for 循环通过使用 file 对象的隐式变量（也就是说，变量不是显式创建的），对这个过程稍做了点儿简化。打开文件和读取文件内容一次完成，生成的效果与第一个显式示例相同。最后一个示例进一步做了简化，并演示了直接在 file 对象上进行迭代的能力（请注意，这是 Python 的一个新特性，所以在您的计算机上可能无法工作）。在这个示例中，创建隐式 file 对象，然后 Python 做余下的工作，允许对文件中的全部行进行迭代。

但是，有些时候，在从文件读取数据时，可能想要更好的控制级别。在这种情况下，应当使用 readline 方法，如清单 5 所示。

清单 5. 读取数据

>>> myfile = open("testit.txt")>>> myfile.readline()'Hello World!/n'>>> myfile.readline()'The total value = $1820.00/n'>>> myfile.readline()''>>> myfile.seek(0)>>> myfile.readline()'Hello World!/n'>>> myfile.tell()13L>>> myfile.readline()'The total value = $1820.00/n'>>> myfile.tell()40L>>> myfile.readline()''>>> myfile.tell()40L>>> myfile.seek(0)>>> myfile.read(17)'Hello World!/nThe '>>> myfile.seek(0)>>> myfile.readlines(23)['Hello World!/n', 'The total value = $1820.00/n']>>> myfile.close()

这个示例演示了如何在文件中移动，一次读取一行，或者显式地用 seek 方法移动文件位置指示器。首先，用 readline 方法在文件行中移动。当到达文件末尾时，readline 方法返回一个空的 string。在过了文件末尾之后，如果还用这种方式继续读取，并不会造成错误，只会返回空的 string。

然后返回文件开始的地方，并读取另一行。 tell 方法显示出在文件中的当前位置（应当在第一行文本之后） —— 在这个示例中，在第 13 个字符位置。通过使用这个知识，可以向 read 或readline 方法传递一个参数，控制读取的字符数。对于 read 方法，这个参数（在这个示例中是 17）是要从文件中读取的字符数。但是 readline 方法在读入指定数量的字符后，还会继续读取，直到行尾。在这个示例中，它读取第一行和第二行文本。

写入数据

迄今为止的示例都侧重于读取数据，而不是写入数据。但是如清单 6 所示，一旦了解了使用 file 对象的基础知识，写入也很容易。

清单 6. 写入数据

  >>> mydata = ['Hello World!', 'The total value = $1820.00']>>> myfile = open('testit.txt', 'w')>>> for line in mydata:...     myfile.write(line + '/n')... >>> myfile.close()>>> myfile = open("testit.txt")>>> myfile.read()'Hello World!/nThe total value = $1820.00/n'>>> myfile.close()>>> myfile = open("testit.txt", "r+")>>> for line in mydata:...     myfile.write(line + '/n')... >>> myfile.seek(0)>>> myfile.read()'Hello World!/nThe total value = $1820.00/n'>>> myfile.close()>>> myfile = open("testit.txt", "r+a")>>> myfile.read()'Hello World!/nThe total value = $1820.00/n'>>> for line in mydata:...     myfile.write(line + '/n')... >>> myfile.seek(0)>>> myfile.read()'Hello World!/nThe total value = $1820.00/nHello World!/nThe total value = $1820.00/n'>>> myfile.close()

要把数据写入文件，必须先创建 file 对象。但是，在这情况下，必须用 'w' 模式标记指定要写入文件。在这个示例中，把 mydata list 的内容写入文件，关闭文件，然后重新打开文件，这样就可以读取内容了。

但是，通常情况下，想要同时读取文件和写入文件，所以这个示例的下一部分用 'r+' 模式重新打开文件。因为能够写入文件，而不是添加，所以文件会被截断。首先，把 mydata list 的内容写入文件，然后把文件指针重新定位到文件开头，并读入内容。然后这个示例关闭文件，并用读取和添加模式 "r+a" 重新打开文件。正如示例代码所示，文件内容现在是两个写入操作的结果（文本是重复的）。

处理二进制数据

前面所有的示例都处理文本数据或字符数据：写入和读取字符 string。但是，在某些情况下，例如在处理整数或压缩文件时，需要能够读取和写入二进制数据。在创建 file 对象时，通过把 'b' 添加到文件模式中，可以很容易地用 Python 处理二进制数据，如清单 7 所示。

清单 7. 处理二进制数据

>>> myfile = open("testit.txt", "wb")>>> for c in range(50, 70):...     myfile.write(chr(c))... >>> myfile.close()>>> myfile = open("testit.txt")>>> myfile.read()'23456789:;<=>?@ABCDE'>>> myfile.close()

在这个示例中，创建一个合适的 file 对象，然后用从 50 到 69 的 ASCII 值写入二进制字符。使用 chr 方法，把 range 方法调用创建的整数转变成字符。在写完所有数据之后，关闭文件并重新打开文件进行读取，还是使用二进制模式标记。读取文件可以证明没有把整数写入文件，相反，写的是字符值。

在读取和写入二进制数据时，必须小心，因为不同的平台用不同的方式保存二进制数据。如果必须处理二进制数据，最好是使用来自 Python 库的合适对象（或者来自第三方开发人员的对象）。

回页首

读取和写入：最有趣的地方

这篇文章讨论了在 Python 程序中如何从文件读取数据和写入数据到文件中。总体来说，过程很简单：创建合适的 file 对象，然后按照需要读取和写入。但是，在使用写入模式创建 file 文件，向文件写入数据时，必须注意文件的截断。如果需要向文件中添加数据，应当在创建 file 对象时，使用添加模式。

http://www.ibm.com/developerworks/cn/opensource/os-python8/

--------------------------------------------------------------------------------------------------

 

Writing MySQL Scripts with Python DB-API

Paul DuBois
[e-mail]

Document revision: 1.02
Last update: 2006-09-17

Table of Contents

• MySQLdb Installation
• A Short DB-API Script
  • Writing the Script
  • Running the Script
• A More Extensive DB-API Script
  • Error Handling
  • Methods for Issuing Statements
• Portability Notes
• Resources
• Appendix
• Acknowledgment
• Revision History

Python is one of the more popular Open Source programming languages, owing largely to its own native expressiveness as well as to the variety of support modules that are available to extend its capabilities. One of these modules is DB-API, which, as the name implies, provides a database application programming interface. DB-API is designed to be relatively independent of details specific to any given database engine, to help you write database-access scripts that are portable between engines.

DB-API's design is similar to that used by Perl and Ruby DBI modules, the PHP PEAR DB class, and the Java JDBC interface: It uses a two-level architecture in which the top level provides an abstract interface that is similar for all supported database engines, and a lower level consisting of drivers for specific engines that handle engine-dependent details. This means, of course, that to use DB-API for writing Python scripts, you must have a driver for your particular database system. For MySQL, DB-API provides database access by means of the MySQLdb driver. This document begins by discussing driver installation (in case you don't have MySQLdb), then moves on to cover how to write DB-API scripts.

MySQLdb Installation

To write MySQL scripts that use DB-API, Python itself must be installed. That will almost certainly be true if you're using Unix, but is less likely for Windows. Installers for either platform can be found on the Python web site (see the "Resources" section at the end of this document).

Verify that your version of Python is 2.3.4 or later, and that the MySQLdb module is installed. You can check both of these requirements by running Python in interactive mode from the command line prompt (something like % for Unix or C:/> for Windows):

   % python   Python 2.4.3 (#1, Aug 29 2006, 14:45:33)   [GCC 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0, pie-8.7.9)] on linux2   Type "help", "copyright", "credits" or "license" for more information.   >>> import MySQLdb

Assuming that you have a recent enough version of Python and that no error occurs when you issue the import MySQLdb statement, you're ready to begin writing database-access scripts and you can skip to the next section. However, if you get the following error, you need to obtain and install MySQLdb first:

   >>> import MySQLdb   Traceback (most recent call last):     File "", line 1, in ?   ImportError: No module named MySQLdb

To obtain MySQLdb, visit the "Resources" section to see where to fetch a distribution appropriate for your system. Precompiled binaries may available for your platform, or you can install from source. If you use a binary distribution, install it using your platform's usual package installation procedure. To build and install from source, move into the top-level directory of the MySQLdb distribution and issue the following commands. (Under Unix, it's likely that you'll need to run the second command as root so that the driver files can be copied into your Python installation.)

   % python setup.py build   % python setup.py install

If you encounter problems, check the README file included with the MySQLdb distribution.

A Short DB-API Script

Scripts that access MySQL through DB-API using MySQLdb generally perform the following steps:

• Import the MySQLdb module
• Open a connection to the MySQL server
• Issue statements and retrieve their results
• Close the server connection

The rest of this section presents a short DB-API script that illustrates the basic elements of these steps. Later sections discuss specific aspects of script-writing in more detail.

Writing the Script

Use a text editor to create a file named server_version.py that contains the following script. This script uses MySQLdb to interact with the MySQL server, albeit in relatively rudimentary fashion--all it does is ask the server for its version string:

   # server_version.py - retrieve and display database server version   import MySQLdb   conn = MySQLdb.connect (host = "localhost",                           user = "testuser",                           passwd = "testpass",                           db = "test")   cursor = conn.cursor ()   cursor.execute ("SELECT VERSION()")   row = cursor.fetchone ()   print "server version:", row[0]   cursor.close ()   conn.close ()

The import statement tells Python that the script needs to use the code in the MySQLdb module. This statement must precede any attempt to connect to the MySQL server. Then the connection is established by invoking the connect() method of the MySQLdb driver with the proper connection parameters. These include the hostname where the server is running, the username and password for your MySQL account, and the name of the database that you want to use. connect() argument list syntax varies among drivers; for MySQLdb, the arguments are allowed to be given in name = value format, which has the advantage that you can specify them in any order. server_version.pymakes a connection to the MySQL server on the local host to access the test database with a username and password of testuser and testpass:

   conn = MySQLdb.connect (host = "localhost",                           user = "testuser",                           passwd = "testpass",                           db = "test")

If the connect() call succeeds, it returns a connection object that serves as the basis for further interaction with MySQL. If the call fails, it raises an exception. (server_version.py doesn't handle the exception, so an error at this point terminates the script. Error handling is covered later in this document.)

After the connection object has been obtained, server_version.py invokes its cursor() method to create a cursor object for processing statements. The script uses this cursor to issue a SELECT VERSION() statement, which returns a string containing server version information:

   cursor = conn.cursor ()   cursor.execute ("SELECT VERSION()")   row = cursor.fetchone ()   print "server version:", row[0]   cursor.close ()

The cursor object's execute() method sends the statement to the server and fetchone() retrieves a row as a tuple. For the statement shown here, the tuple contains a single value, which the script prints. (If no row is available, fetchone() actually will return the value None; server_version.py blithely assumes that this won't happen, an assumption that you normally should not make. In later examples, we'll handle this case.) Cursor objects can be used to issue multiple statements, butserver_version.py has no more need for cursor after getting the version string, so it closes it.

Finally, the script invokes the connection object's close() method to disconnect from the server:

   conn.close ()

After that, conn becomes invalid and should not be used to access the server.

Running the Script

To execute the server_version.py script, invoke Python from the command line prompt and tell it the script name. You should see a result something like this:

   % python server_version.py   server version: 5.1.12-beta-log

This indicates that the MySQL server version is 5.1.12; the -beta and -log suffixes tell us the distribution stability level and that query logging is enabled. (You might see other suffixes than those shown here. For example, if you have debugging enabled, you'll see a -debug suffix.)

It's possible to set up the script so that it can be run by name without invoking Python explicitly. Under Unix, add an initial #! line to the script that specifies the full pathname of the Python interpreter. This tells the system what program should execute the script. For example, if Python lives at /usr/bin/python on your system, add the following as the first line of the script:

   #!/usr/bin/python

Then use chmod to make the script executable, and you'll be able to run it directly:

   % chmod +x server_version.py   % ./server_version.py

(The leading "./" tells your command interpreter explicitly that the script is located in your current directory. Many Unix accounts are set up not to search the current directory when looking for commands.)

Under Windows, the #! line is unnecessary (although it's harmless, so you need not remove it if you write the script on a Unix system and then move it to a Windows box). Instead, you can set up a filename association so that .py scripts will be associated with Python. Instead of using chmod to make the script executable, open the Folder Options item in the Control Panel and select its File Types tab. File Types enables you to set up an association for files that end with .py to tell Windows to execute them with Python. Then you can invoke the script by name:

   C:/> server_version.py

If you install ActiveState Python on Windows, the ActiveState installer sets up the association automatically as part of the installation process.

A More Extensive DB-API Script

server_version.py has a number of shortcomings. For example, it doesn't catch exceptions or indicate what went wrong if an error occurs, and it doesn't allow for the possibility that the statement it runs might not return any results. This section shows how to address these issues using a more elaborate script, animal.py, that uses a table containing animal names and categories:

   CREATE TABLE animal   (     name     CHAR(40),     category CHAR(40)   )

If you've read the PEAR DB document available at the Kitebird site (see "Resources"), you might recognize this table and some of the statements issued by animal.py; they were used in that document, too.

The animal.py script begins like this (including the #! line, should you intend to run the script on a Unix system):

   #!/usr/bin/python   # animal.py - create animal table and   # retrieve information from it   import sys   import MySQLdb

As with server_version.py, the script imports MySQLdb, but it also imports the sys module for use in error handling. (animal.py uses sys.exit() to return 1 to indicate abnormal termination if an error occurs.)

Error Handling

After importing the requisite modules, animal.py establishes a connection to the server using the connect() call. To allow for the possibility of connection failure (for example, so that you can display the reason for the failure), it's necessary to catch exceptions. To handle exceptions in Python, put your code in a trystatement and include an except clause that contains the error-handling code. The resulting connection sequence looks like this:

   try:     conn = MySQLdb.connect (host = "localhost",                             user = "testuser",                             passwd = "testpass",                             db = "test")   except MySQLdb.Error, e:     print "Error %d: %s" % (e.args[0], e.args[1])     sys.exit (1)

The except clause names an exception class (MySQLdb.Error in this example) to obtain the database-specific error information that MySQLdb can provide, as well as a variable (e) in which to store the information. If an exception occurs, MySQLdb makes this information available in e.args, a two-element tuple containing the numeric error code and a string describing the error. The except clause shown in the example prints both values and exits.

Any database-related statements can be placed in a similar try/except structure to trap and report errors; for brevity, the following discussion doesn't show the exception-handling code. (The complete text of animal.py is listed in the Appendix.)

Methods for Issuing Statements

The next section of animal.py creates a cursor object and uses it to issue statements that set up and populate the animal table:

   cursor = conn.cursor ()   cursor.execute ("DROP TABLE IF EXISTS animal")   cursor.execute ("""       CREATE TABLE animal       (         name     CHAR(40),         category CHAR(40)       )     """)   cursor.execute ("""       INSERT INTO animal (name, category)       VALUES         ('snake', 'reptile'),         ('frog', 'amphibian'),         ('tuna', 'fish'),         ('racoon', 'mammal')     """)   print "Number of rows inserted: %d" % cursor.rowcount

Note that this code includes no error checking. (Remember that it will be placed in a try statement; errors will trigger exceptions that are caught and handled in the corresponding except clause, which allows the main flow of the code to read more smoothly.) The statements perform the following actions:

• Drop the animal table if it already exists, to begin with a clean slate.
• Create the animal table.
• Insert some data into the table and report the number of rows added.

Each statement is issued by invoking the cursor object's execute() method. The first two statements produce no result, but the third produces a count indicating the number of rows inserted. The count is available in the cursor's rowcount attribute. (Some database interfaces provide this count as the return value of the statement-execution call, but that is not true for DB-API.)

The animal table is set up at this point, so we can issue SELECT statements to retrieve information from it. As with the preceding statements, SELECT statements are issued using execute(). However, unlike statements such as DROP or INSERT, SELECT statements generate a result set that you must retrieve. That is, execute() only issues the statement, it does not return the result set. You can use fetchone() to get the rows one at a time, or fetchall() to get them all at once. animal.py uses both approaches. Here's how to use fetchone() for row-at-a-time retrieval:

   cursor.execute ("SELECT name, category FROM animal")   while (1):     row = cursor.fetchone ()     if row == None:       break     print "%s, %s" % (row[0], row[1])   print "Number of rows returned: %d" % cursor.rowcount

fetchone() returns the next row of the result set as a tuple, or the value None if no more rows are available. The loop checks for this and exits when the result set has been exhausted. For each row returned, the tuple contains two values (that's how many columns the SELECT statement asked for), which animal.py prints. The printstatement shown above accesses the individual tuple elements. However, because they are used in order of occurrence within the tuple, the print statement could just as well have been written like this:

   print "%s, %s" % row

After displaying the statement result, the script also prints the number of rows returned (available as the value of the rowcount attribute).

fetchall() returns the entire result set all at once as a tuple of tuples, or as an empty tuple if the result set is empty. To access the individual row tuples, iterate through the row set that fetchall() returns:

   cursor.execute ("SELECT name, category FROM animal")   rows = cursor.fetchall ()   for row in rows:     print "%s, %s" % (row[0], row[1])   print "Number of rows returned: %d" % cursor.rowcount

This code prints the row count by accessing rowcount, just as for the fetchone() loop. Another way to determine the row count when you use fetchall() is by taking the length of the value that it returns:

   print "%d rows were returned" % len (rows)

The fetch loops shown thus far retrieve rows as tuples. It's also possible to fetch rows as dictionaries, which enables you to access column values by name. The following code shows how to do this. Note that dictionary access requires a different kind of cursor, so the example closes the cursor and obtains a new one that uses a different cursor class:

   cursor.close ()   cursor = conn.cursor (MySQLdb.cursors.DictCursor)   cursor.execute ("SELECT name, category FROM animal")   result_set = cursor.fetchall ()   for row in result_set:     print "%s, %s" % (row["name"], row["category"])   print "Number of rows returned: %d" % cursor.rowcount

NULL values in a result set are returned as None to your program.

MySQLdb supports a placeholder capability that enables you to bind data values to special markers within the statement string. This provides an alternative to embedding the values directly into the statement. The placeholder mechanism handles adding quotes around data values, and it escapes any special characters that occur within values. The following examples demonstrate an UPDATE statement that changes snake to turtle, first using literal values and then using placeholders. The literal-value statement looks like this:

   cursor.execute ("""         UPDATE animal SET name = 'turtle'         WHERE name = 'snake'       """)   print "Number of rows updated: %d" % cursor.rowcount

Alternatively, you can issue the same statement by using %s placeholder markers and binding the appropriate values to them:

   cursor.execute ("""         UPDATE animal SET name = %s         WHERE name = %s       """, ("snake", "turtle"))   print "Number of rows updated: %d" % cursor.rowcount

Note the following points about the form of the preceding execute() call:

• The %s placeholder marker should occur once for each value that is to be inserted into the statement string.
• No quotes should be placed around the %s markers; MySQLdb supplies quotes for you as necessary.
• Following the statement string argument to execute(), provide a tuple containing the values to be bound to the placeholders, in the order they should appear within the string. If you have only a single value x, specify it as (x,) to indicate a single-element tuple.
• Bind the Python None value to a placeholder to insert an SQL NULL value into the statement.

After issuing the statements, animal.py closes the cursor, commits the changes, and disconnects from the server:

   cursor.close ()   conn.commit ()   conn.close ()

The connection object commit() method commits any outstanding changes in the current transaction to make them permanent in the database. In DB-API, connections begin with autocommit mode disabled, so you must call commit() before disconnecting or changes may be lost.

If the animal table is a MyISAM table, commit() has no effect: MyISAM is a non-transactional storage engine, so changes to MyISAM tables take effect immediately regardless of the autocommit mode. However, if animal uses a transactional storage engine such as InnoDB, failure to invoke commit() results in an implicit transaction rollback when you disconnect. For example, if you add ENGINE=InnoDB to the end of the CREATE TABLE statement and remove the commit() invocation near the end of the script, you'll find that animal is empty after the script runs.

For scripts that only retrieve data, no changes need to be committed and commit() is unnecessary.

Portability Notes

If you want to port a MySQLdb-based DB-API script for use with a different database, sources of non-portability occur anywhere that the driver name might be used:

• The import statement that imports the driver module. This must be changed to import a different driver.
• The connect() call that connects to the database server. The connect() method is accessed through the name of the driver modules, so the driver name needs to be changed. In addition, the connect() argument syntax may vary between drivers.
• Exception handling. The exception class named on except clauses is referenced through the driver name.

Another type of non-portability that does not involve the driver name concerns the use of placeholders. The DB-API specification allows for several placeholder syntaxes, and some drivers use a syntax that differs from the one supported by MySQLdb.

Resources

The scripts that are used for examples in this document can be downloaded from the following location:

   http://www.kitebird.com/articles/

You may find the following additional resources helpful for using Python DB-API and the MySQLdb driver:

• Andy Dustman, author of the MySQLdb module, has a site at:

     http://dustman.net/andy/python/

  That site is the best place to read the MySQLdb documentation and FAQ online. It also has links to Debian and Windows binary distributions. To get source code or Linux RPMs, visit the MySQLdb SourceForge repository at:

     http://sourceforge.net/projects/mysql-python

• MySQL Cookbook includes Python DB-API among the database programming interfaces that it covers:

     http://www.kitebird.com/mysql-cookbook   http://www.oreilly.com/catalog/mysqlckbk/

• The Python web site has installers for the Python language processor, should you be running on a system that doesn't already have it installed:

     http://www.python.org/

• The database SIG (special interest group) area on the Python web site contains additional DB-API information:

     http://www.python.org/sigs/db-sig/

• The animal table used by the animal.py script is also used in the PEAR DB document at the Kitebird site:

     http://www.kitebird.com/articles/

  You might find it instructive to compare that document with this one to see how DB-API and PEAR DB are similar or different in their approaches to database access.

Appendix

The full source code for the animal.py script is shown here:

   #!/usr/bin/python   # animal.py - create animal table and   # retrieve information from it   import sys   import MySQLdb   # connect to the MySQL server   try:     conn = MySQLdb.connect (host = "localhost",                             user = "testuser",                             passwd = "testpass",                             db = "test")   except MySQLdb.Error, e:     print "Error %d: %s" % (e.args[0], e.args[1])     sys.exit (1)   # create the animal table and populate it   try:     cursor = conn.cursor ()     cursor.execute ("DROP TABLE IF EXISTS animal")     cursor.execute ("""         CREATE TABLE animal         (           name     CHAR(40),           category CHAR(40)         )       """)     cursor.execute ("""         INSERT INTO animal (name, category)         VALUES           ('snake', 'reptile'),           ('frog', 'amphibian'),           ('tuna', 'fish'),           ('racoon', 'mammal')       """)     print "Number of rows inserted: %d" % cursor.rowcount   # perform a fetch loop using fetchone()     cursor.execute ("SELECT name, category FROM animal")     while (1):       row = cursor.fetchone ()       if row == None:         break       print "%s, %s" % (row[0], row[1])     print "Number of rows returned: %d" % cursor.rowcount   # perform a fetch loop using fetchall()     cursor.execute ("SELECT name, category FROM animal")     rows = cursor.fetchall ()     for row in rows:       print "%s, %s" % (row[0], row[1])     print "Number of rows returned: %d" % cursor.rowcount   # issue a statement that changes the name by including data values   # literally in the statement string, then change the name back   # by using placeholders     cursor.execute ("""           UPDATE animal SET name = 'turtle'           WHERE name = 'snake'         """)     print "Number of rows updated: %d" % cursor.rowcount     cursor.execute ("""           UPDATE animal SET name = %s           WHERE name = %s         """, ("snake", "turtle"))     print "Number of rows updated: %d" % cursor.rowcount   # create a dictionary cursor so that column values   # can be accessed by name rather than by position     cursor.close ()     cursor = conn.cursor (MySQLdb.cursors.DictCursor)     cursor.execute ("SELECT name, category FROM animal")     result_set = cursor.fetchall ()     for row in result_set:       print "%s, %s" % (row["name"], row["category"])     print "Number of rows returned: %d" % cursor.rowcount     cursor.close ()   except MySQLdb.Error, e:     print "Error %d: %s" % (e.args[0], e.args[1])     sys.exit (1)   conn.commit ()   conn.close ()

Acknowledgment

The original version of this document was written for NuSphere Corporation. The current version is an updated revision of the original.

Revision History

• 1.00--Original version.
• 1.01, 2003-01-24--Minor revisions.
• 1.02, 2006-09-17--Bring up to date for MySQLdb 1.2.1. Add information about autocommit mode and the commit() method.

 

http://www.kitebird.com/articles/pydbapi.html

 

python print 输出到文件中

c="a string to print to file"
f=open('out.txt','w')
print >>f,c
f.close()
注意>>f后面要加逗号，否则会出错

-------------------------------------------------------------------------------------------------------------

MySQLdb默认查询结果都是返回tuple，输出时候不是很方便，必须按照0，1这样读取，无意中在网上找到简单的修改方法，就是传递一个cursors.DictCursor就行。

默认程序： import MySQLdb db = MySQLdb.connect(host = 'localhost', user = 'root', passwd = '123456', db = 'test') cursor = db.cursor() cursor.execute('select * from user') rs = cursor.fetchall() print rs # 返回类似如下 # ((1000L, 0L), (2000L, 0L), (3000L, 0L))

修改后： import MySQLdb import MySQLdb.cursors db = MySQLdb.connect(host = 'localhost', user = 'root', passwd = '123456', db = 'test',cursorclass = MySQLdb.cursors.DictCursor) cursor = db.cursor() cursor.execute('select * from user') rs = cursor.fetchall() print rs # 返回类似如下 # ({'age': 0L, 'num': 1000L}, {'age': 0L, 'num': 2000L}, {'age': 0L, 'num': 3000L})

或者也可以用下面替换connect和cursor部分 db = MySQLdb.connect(host = 'localhost', user = 'root', passwd = '123456', db = 'test') cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

 

------------------------------------------------------------------------------------------------

python连接MySQL数据库方法介绍

模块功能：connect()方法
        * connect()方法用于连接数据库，返回一个数据库连接对象。如果要连接一个位于host.remote.com服务器上名为fourm的MySQL数据库，连接串可以这样写：
        db = MySQLdb.connect(host="remote.com",user="user",passwd="xxx",db="fourm" )
connect()的参数列表如下：
        host，连接的数据库服务器主机名，默认为本地主机(localhost)。   
        user，连接数据库的用户名，默认为当前用户。
        passwd，连接密码，没有默认值。
        db，连接的数据库名，没有默认值。
        conv，将文字映射到Python类型的字典。默认为MySQLdb.converters.conversions
        cursorclass，cursor()使用的种类，默认值为MySQLdb.cursors.Cursor。
        compress，启用协议压缩功能。
        named_pipe，在windows中，与一个命名管道相连接。
        init_command，一旦连接建立，就为数据库服务器指定一条语句来运行。              
        read_default_file，使用指定的MySQL配置文件。
        read_default_group，读取的默认组。
        unix_socket，在unix中，连接使用的套接字，默认使用TCP。
        port，指定数据库服务器的连接端口，默认是3306。
* 连接对象的db.close()方法可关闭数据库连接，并释放相关资源。
* 连接对象的db.cursor([cursorClass])方法返回一个指针对象，用于访问和操作数据库中的数据。
* 连接对象的db.begin()方法用于开始一个事务，如果数据库的AUTOCOMMIT已经开启就关闭它，直到事务调用commit()和rollback()结束。
*连接对象的db.commit()和db.rollback()方法分别表示事务提交和回退。
*指针对象的cursor.close()方法关闭指针并释放相关资源。
*指针对象的cursor.execute(query[,parameters])方法执行数据库查询。
*指针对象的cursor.fetchall()可取出指针结果集中的所有行，返回的结果集一个元组(tuples)。
*指针对象的cursor.fetchmany([size=cursor.arraysize])从查询结果集中取出多行，我们可利用可选的参数指定取出的行数。
*指针对象的cursor.fetchone()从查询结果集中返回下一行。
*指针对象的cursor.arraysize属性指定由cursor.fetchmany()方法返回行的数目，影响fetchall()的性能，默认值为1。
*指针对象的cursor.rowcount属性指出上次查询或更新所发生行数。-1表示还没开始查询或没有查询到数据。
模块功能演示
#!/usr/bin/python
import MySQLdb
try:
   connection = MySQLdb.connect(user="user",passwd="password",host="xxx",db="test")
except:
   print "Could not connect to MySQL server."
   exit( 0 )
try:
   cursor = connection.cursor()
   cursor.execute( "SELECT note_id,note_detail FROM note where note_id = 1" )
   print "Rows selected:", cursor.rowcount
   for row in cursor.fetchall():
       print "note : ", row[0], row[1]
   cursor.close()

 

http://www.pyfun.com/rumen/201010/2237.html

-----------------------------------------------------------------

分享一点python 编码设置的知识

文章分类:Python编程

昨天我写了个python 操作mysql的方法，按照文档的提供的参数来写，写完了以后自己测试发现报了一个编码的错误，也不知道为什么会报这个错误，我一般对编码的错误比较在意，原来搞php的时候，就出现过读取数据库里的信息编码错误，后来我吸取了教训，用php读取数据库数据按照一套标准来走就没有出现过编码的问题。
我的标准是：设置4个地方编码一致。
1数据库编码
2数据表编码
3程序文件编码
4读取数据之前设置好编码
我现在用python读取mysql数据库的时候就遇到：数据库错误: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128)
我检查了下各处的编码，数据库，数据表和文件的编码都是utf-8，都是一致的，那为什么还是会出现编码的问题呢？
我想应该是读取数据之前没有设置默认的编码,接着我查了下文档,发现了解决办法。
我通过sys模块的setdefaultencoding('编码类型')来进行python 编码设置。
下面给出我的代码：
#encoding=utf-8
import sys
import MySQLdb
reload(sys)
sys.setdefaultencoding('utf-8')
看来应该先设置好编码类型，再读取数据就不会出错了。

作者:老王@python python 教程 老王python,提供python相关的python 书籍,python 主机,django 教程和python 下载，希望大家能够喜欢!

 

----------------------------------------------------------------------------

使用python读取mysql表结构，并生成oracle建表语句

 

这一周初学python语言，第一次用python来连接mysql，然后读取mysql中的表结构，生成oracle的建表语句，初学，写得不好的地方请大家指正。

ps：这个只能还没做到很好，一些外键等约束条件还没有加上，还有表结构的索引之类的也没有

#!/usr/bin/python

# connect.py – connect to the MySQL server

import sys

#导入MySql模块

import MySQLdb

#MySql的类型跟Oracle的类型不一样，生成SQL语句时要进行一些类型的转换

def mysql2oracle(str):

str_copy = str.upper();

str_oracle = str.upper();

if str_copy.startswith(’TINYINT’):

str_oracle = ‘NUMBER’;

elif str_copy.startswith(’SMALLINT’):

str_oracle = ‘NUMBER’;

elif str_copy.startswith(’INT’):

str_oracle = ‘NUMBER’;

elif str_copy.startswith(’MEDIUMINT’):

str_oracle = ‘NUMBER’

elif str_copy.startswith(’BITINT’):

str_oracle = ‘NUMBER’;

elif str_copy.startswith(’VARCHAR’):

str_oracle = ‘VARCHAR2′+str[len('VARCHAR'):];

elif str_copy.startswith(’DATETIME’):

str_oracle = ‘DATE’;

elif str_copy.startswith(’FLOAT’):

str_oracle = ‘NUMBER’+str[len('FLOAT'):];

elif str_copy.startswith(’DOUBLE’):

str_oracle = ‘NUMBER’+str[len('DOUBLE'):];

elif str_copy.startswith(’TEXT’):

str_oracle = ‘VARCHAR2(4000)’;

elif str_copy.startswith(’CHAR’):

pass;

elif str_copy.startswith(’TIMESTAMP’):

pass;

else:

print ‘unkown field type ‘

return ‘wrong’;

return str_oracle;

#连接mysql数据库

def mysql_conn():

global conn;

try:

conn = MySQLdb.connect (db = “”,#要连接的数据库名

host = “”,#计算机地址

user = “”,#用户

passwd = “”)#密码

print “Connected”

except MySQLdb.Error,e:

print “Cannot connect to server”

print “Error code:”, e.args[0]

print “Error message:”, e.args[1]

sys.exit (1)

conn.close ()

print “Disconnected”

#使用desc命令读取表结构，并将其解析成oracle的建表语句

#输入表名，最后生成“表名.sql”的文件，一个文件表示一个建表语句

def desc_commit(tablename):

global conn;

cursor =conn.cursor();

cursor.execute(’desc ‘ + tablename);

result = cursor.fetchall()

f = file(tablename+’.sql’, ‘w’)

sql_create=’CREATE TABLE ‘ + tablename +’(/n’;

for record in result:

sql_create += record[0] + ‘ ‘

strtmp = mysql2oracle(record[1]);

if strtmp==’wrong’:

print tablename;

return;

else:

sql_create+=strtmp;

if record[3]==’PRI’:

sql_create += ‘ PRIMARY KEY ‘

if str(record[4])!=’None’:

if str(record[4])==’0000-00-00 00:00:00′:

sql_create += ‘ DEFAULT SYSDATE’

else:

sql_create += ‘ DEFAULT /”+str(record[4])+’/”

if record[2]==’NO’:

sql_create += ‘ NOT NULL ‘

sql_create+=’,/n’

f.write(sql_create[:len(sql_create)-2] +’);/n’);

f.close();

#main function主函数（调用）:

tablename = ‘t’;

tables=(’aaa’,’bbb’,’ccc’’)#表名

conn = None;

mysql_conn();

for one_table in tables:

desc_commit(one_table)

http://www.alidw.com/?p=904