MYSQLDB Installation and use 2

Http://blog.chinaunix.net/uid-8487640-id-3183185.html

MySQLdb is the Python connection MySQL module, the following describes the source mode installation MySQLdb:

1. to download the download first: please go to the official website http://sourceforge.net/projects/mysql-python/or click on the link to download http://downloads.sourceforge.net/ PROJECT/MYSQL-PYTHON/MYSQL-PYTHON-TEST/1.2.3C1/MYSQL-PYTHON-1.2.3C1.TAR.GZ?USE_MIRROR=NCHC
3. Unzip: Tar zxvf mysql-python*
5. into the file directory, run the following command:
   python setup.py install 
7. installation complete, to The Site-packages directory in your Python installation directory checks to see if the following files exist, which means the installation succeeded
   Linux:mysql_python-1.2.3c1-py2.6-linux-i686.egg
   Mac OS X:mysql_python-1.2.3c1-py2.6-macosx-10.4-x86_64.egg
   Note: If you encounter mysql_config not found problem, there are two ways to fix it:
   1) ln-s/ Usr/local/mysql/bin/mysql_config/usr/local/bin/mysql_config
   Link Mysql_confi from your installation directory to the/usr/local/bin directory, This can be accessed in any directory (also can put to/usr/bin)
   2) Edit the source folder site.cfg file, remove #mysql_config =/usr/local/bin/mysql_config before the comment #, Modify the following path for your mysql_config real directory on it. (If you don't know where Mysql_config is, run the command: Whereis mysql_config)

Note: If you encounter import Error:libmysqlclient.so.18:cannot Open shared object file:no such file or directory

Workaround: Locate or find libmysqlclient.so.18

Link path/libmysqlclient.so.18/usr/lib/libmysqlclient.so.18

vi/etc/ld.so.conf //Join the directory where libmysqlclient.so.18 is located

Insertion:/usr/lib/

Save exit after execution/sbin/ldconfig effective

1. Test method
   1) Run command python into Python runtime environment
   2) Enter the following Python code to test
   Import MySQLdb
2. Test=mysqldb.connect (db= ' mydb ', host= ' myhost ', user= ' u ', passwd= ' P ')
3. cur = test.cursor ()
4. Cur.execute (' show databases; ')
5. For data in Cur.fetchall ():
6. Print data
7. 3) If you see the output of the library name of several databases on your screen, you have successfully installed it.
8. Problems that may be encountered
   1) Problem: Importerror:libmysqlclient_r.so.16:cannot Open Shared object file:no such file or directory
   The reason is that Python cannot find the libmysqlclient_r.so.16 dynamic library under the MySQL directory, in fact MySQLdb is the C function library that calls MySQL. So the first thing on this machine is to install MySQL.
   Then: Export ld_library_path=/usr/local/mysql/lib/mysql: $LD _library_path
   and put/usr/local/mysql5.1/lib/mysql into/etc/ld.so.conf.
   The contents of the/etc/ld.so.conf after the change are:
   Include ld.so.conf.d/*.conf
   /usr/local/mysql5.1/lib/mysql
   The last test again, there will be no problem.

MySQLdb Operation:

Python code

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

#!/usr/bin/env python

#coding =utf-8

##################################

#MySQLdb Example #

##################################

Import MySQLdb

#建立和数据库系统的连接

conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' longforfreedom ')

#获取操作游标

cursor = Conn.cursor ()

#执行SQL, create a database.

Cursor.execute ("" "Create Database Python" ")

#关闭连接, freeing up resources

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. #MySQLdb Example
10. #
12. ##################################
14. Import MySQLdb
18. #建立和数据库系统的连接
20. conn = MySQLdb.connect (host=' localhost ', user=' root ', passwd=' longforfreedom ')
24. #获取操作游标
26. cursor = Conn.cursor ()
28. #执行SQL, create a database.
30. Cursor.execute ("" "CREATE database if not exists Python " ")
34. #选择数据库
36. conn.select_db (' python ');
38. #执行SQL, create a data table.
40. Cursor.execute ("" "CREATE TABLE test (ID int, info varchar )" "")
44. Value = [1,"inserted?"];
48. #插入一条记录
50. Cursor.execute ("INSERT into test values (%s,%s)", value);
54. Values=[]
60. #生成插入参数值
62. For I in range:
64. Values.append ((i,' Hello mysqldb, I am recoder ' + str (i)))
66. #插入多条记录
70. Cursor.executemany ("" "insert INTO test values (%s,%s)" ", values);
74. #关闭连接, freeing up resources
76. Cursor.close ();

#!/usr/bin/env python

#coding =utf-8

###################################

#MySQLdb Example #

##################################

Import MySQLdb

#建立和数据库系统的连接

conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' longforfreedom ')

#获取操作游标

cursor = Conn.cursor ()

#执行SQL, create a database.

Cursor.execute ("" "CREATE database if not exists Python" ")

#选择数据库

conn.select_db (' Python ');

#执行SQL, create a data table.

Cursor.execute ("" "CREATE TABLE test (ID int, info varchar (100))" ")

Value = [1, "inserted?"];

#插入一条记录

Cursor.execute ("INSERT into test values (%s,%s)", value);

Values=[]

#生成插入参数值

For I in range (20):

Values.append ((i, ' Hello mysqldb, I am recoder ' + str (i)));

#插入多条记录

Cursor.executemany ("" "INSERT into test values (%s,%s)" ", values);

#关闭连接, freeing up resources

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. # MYSQLDB Query
10. #
12. #######################################
16. Import MySQLdb
18. conn = MySQLdb.connect (host=' localhost ', user=' root ', passwd=' longforfreedom ', db=' python ')
22. cursor = Conn.cursor ()
24. Count = Cursor.execute (' select * from Test ')
28. Print ' has a total of%s records ', Count
30. #获取一条记录, each record is returned as a single tuple
32. Print "Get only one record:"
34. result = Cursor.fetchone ();
36. Print result
38. #print ' ID:%s info:%s '% (Result[0],result[1])
40. Print ' ID:%s info:%s '% result
44. #获取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
46. Print "Get only 5 records:"
48. Results = Cursor.fetchmany (5)
50. For R in results:
52. Print R
56. Print "Get all results:"
58. #重置游标位置, 0, offset, Mode=absolute | Relative, default is relative,
60. Cursor.scroll (0,mode=' absolute ')
62. #获取所有结果
64. Results = Cursor.fetchall ()
66. For R in results:
68. Print R
70. Conn.close ()

The default MySQLdb returns a tuple, which is not friendly to the user and is not conducive to maintenance
Here's how it's resolved

2. Import MySQLdb
4. Import Mysqldb.cursors
8. conn = MySQLdb.connect (
10. host = ' localhost ', user = ' root ',
12. passwd = ", db = ' Test ', compress = 1,
14. Cursorclass = MySQLdb.cursors.DictCursor, charset= ' UTF8 ') // <-Important
18. cursor = Conn.cursor ()
20. Cursor.execute ("Select name, txt from table")
22. rows = Cursor.fetchall ()
24. Cursor.close ()
26. Conn.close ()
30. For row in rows:
32. Print row [' name '], row [' txt '] # bingo!

1. # another (even better) is:
3. conn = MySQLdb. Connect (
4. host = ' localhost ', user = ' root ',
5. passwd = ", db = ' Test ', compress = 1)
6. cursor = conn.cursor (Cursorclass = MySQLdb.cursors.DictCursor)
7. # ...
8. # Results by field name
9. cursor = Conn.cursor ()
10. # ...
11. # ... results by field number

MYSQLDB Installation and use 2