manipulating MySQL with Python

Usually the main programming language is Java, the development of the main use of MySQL, often in order to test, debugging purposes need to operate the database, such as backup, insert test data, modify test data, some times can not be simple with SQL to complete the task, or are very good to complete the task, write in Java and a little too troublesome , I think of Python. Python syntax is concise, without compiling, can be better to complete the task. Today, I looked at Python's operation on MySQL and made a note of it.

First of all, install the required environment, MySQL and Python will not say, necessary things.

Mainly installs the MySQLdb, may go to sf.net to download, the specific address is http://sourceforge.net/projects/mysql-python/

If you use Ubuntu, direct

sudo apt-get install Python-mysqldb

After the installation is complete, you can test it in the Python interpreter

Input

Python code

1. Import mysqldb #注意大小写!!

If you do not make an error, it proves that the installation was successful and may continue

MySQLdb in Python is equivalent to the Java MySQL JDBC Driver,python also has a similar data interface Specification Python DB API,MYSQLDB is the implementation of MySQL. The operation is also relatively simple and other platforms or language operations database, is to establish a connection to the database system, and then to the database input SQL, and then get the results from the database.

First write the simplest, create a database:

Python code

2. #!/usr/bin/env python
4. #coding =utf-8
6. ###################################
8. # @author Migle
10. # @date 2010-01-17
12. ##################################
14. #MySQLdb Example
16. #
18. ##################################
20. Import MySQLdb
24. #建立和数据库系统的连接
26. conn = MySQLdb.connect (host=' localhost ', user=' root ', passwd=' longforfreedom ')
30. #获取操作游标
32. cursor = Conn.cursor ()
34. #执行SQL, create a database.
36. Cursor.execute ("" "Create Database Python" ")
40. #关闭连接, freeing up resources
42. Cursor.close ();

Create a database, create a table, insert data, insert multiple data

Python code

2. #!/usr/bin/env python
4. #coding =utf-8
6. ###################################
8. # @author Migle
10. # @date 2010-01-17
12. ##################################
14. #MySQLdb Example
16. #
18. ##################################
20. Import MySQLdb
24. #建立和数据库系统的连接
26. conn = MySQLdb.connect (host=' localhost ', user=' root ', passwd=' longforfreedom ')
30. #获取操作游标
32. cursor = Conn.cursor ()
34. #执行SQL, create a database.
36. Cursor.execute ("" "Create database if not exists Python " ")
40. #选择数据库
42. conn.select_db (' python ');
44. #执行SQL, create a data table.
46. Cursor.execute ("" "CREATE TABLE test (ID int, info varchar )" "")
50. Value = [1,"inserted?"];
54. #插入一条记录
56. Cursor.execute ("INSERT into test values (%s,%s)", value);
60. Values=[]
66. #生成插入参数值
68. For I in range:
70. Values.append ((i,' Hello mysqldb, I am recoder ' + str (i)))
72. #插入多条记录
76. Cursor.executemany ("" "insert into test values (%s,%s)" ", values);
80. #关闭连接, freeing up resources
82. Cursor.close ();

The process of querying and inserting is similar, just one more step to get the results of the query

Python code

2. #!/usr/bin/env python
4. #coding =utf-8
6. ######################################
8. #
10. # @author Migle
12. # @date 2010-01-17
14. #
16. ######################################
18. #
20. # MYSQLDB Query
22. #
24. #######################################
28. Import MySQLdb
32. conn = MySQLdb.connect (host=' localhost ', user=' root ', passwd=' longforfreedom ', db=' python ')
36. cursor = Conn.cursor ()
40. Count = Cursor.execute (' select * from Test ')
44. Print ' has a total of%s records ', Count
48. #获取一条记录, each record is returned as a single tuple
50. Print "Get only one record:"
52. result = Cursor.fetchone ();
54. Print result
56. #print ' ID:%s info:%s '% (Result[0],result[1])
58. Print ' ID:%s info:%s '% result
62. #获取5条记录, note that because Fetchone () was previously executed, the cursor already refers to the second record, that is, all records starting from the second bar
64. Print "Get only 5 records:"
66. Results = Cursor.fetchmany (5)
68. For R in results:
70. Print R
74. Print "Get all results:"
76. #重置游标位置, 0, offset, Mode=absolute | Relative, default is relative,
78. Cursor.scroll (0,mode=' absolute ')
80. #获取所有结果
82. Results = Cursor.fetchall ()
84. For R in results:
86. Print R
88. Conn.close ()

manipulating MySQL with Python