MYSQLDB Installation and use 2

Source: Internet
Author: User

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
    2. Unzip: Tar zxvf mysql-python*
    3. into the file directory, run the following command:
      python setup.py install 
    4. 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
  1. #!/usr/bin/env python
  2. #coding =utf-8
  3. ###################################
  4. #MySQLdb Example
  5. #
  6. ##################################
  7. Import MySQLdb
  8. #建立和数据库系统的连接
  9. conn = MySQLdb.connect (host=' localhost ', user=' root ', passwd=' longforfreedom ')
  10. #获取操作游标
  11. cursor = Conn.cursor ()
  12. #执行SQL, create a database.
  13. Cursor.execute ("" "Create Database Python" ")
  14. #关闭连接, freeing up resources
  15. 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:
  1. #!/usr/bin/env python
  2. #coding =utf-8
  3. ###################################
  4. #MySQLdb Example
  5. #
  6. ##################################
  7. Import MySQLdb
  8. #建立和数据库系统的连接
  9. conn = MySQLdb.connect (host=' localhost ', user=' root ', passwd=' longforfreedom ')
  10. #获取操作游标
  11. cursor = Conn.cursor ()
  12. #执行SQL, create a database.
  13. Cursor.execute ("" "CREATE database if not exists Python " ")
  14. #选择数据库
  15. conn.select_db (' python ');
  16. #执行SQL, create a data table.
  17. Cursor.execute ("" "CREATE TABLE test (ID int, info varchar )" "")
  18. Value = [1,"inserted?"];
  19. #插入一条记录
  20. Cursor.execute ("INSERT into test values (%s,%s)", value);
  21. Values=[]
  22. #生成插入参数值
  23. For I in range:
  24. Values.append ((i,' Hello mysqldb, I am recoder ' + str (i)))
  25. #插入多条记录
  26. Cursor.executemany ("" "insert INTO test values (%s,%s)" ", values);
  27. #关闭连接, freeing up resources
  28. 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:
  1. #!/usr/bin/env python
  2. #coding =utf-8
  3. #
  4. # MYSQLDB Query
  5. #
  6. #######################################
  7. Import MySQLdb
  8. conn = MySQLdb.connect (host=' localhost ', user=' root ', passwd=' longforfreedom ', db=' python ')
  9. cursor = Conn.cursor ()
  10. Count = Cursor.execute (' select * from Test ')
  11. Print ' has a total of%s records ', Count
  12. #获取一条记录, each record is returned as a single tuple
  13. Print "Get only one record:"
  14. result = Cursor.fetchone ();
  15. Print result
  16. #print ' ID:%s info:%s '% (Result[0],result[1])
  17. Print ' ID:%s info:%s '% result
  18. #获取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
  19. Print "Get only 5 records:"
  20. Results = Cursor.fetchmany (5)
  21. For R in results:
  22. Print R
  23. Print "Get all results:"
  24. #重置游标位置, 0, offset, Mode=absolute | Relative, default is relative,
  25. Cursor.scroll (0,mode=' absolute ')
  26. #获取所有结果
  27. Results = Cursor.fetchall ()
  28. For R in results:
  29. Print R
  30. 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

  1. Import MySQLdb
  2. Import Mysqldb.cursors
  3. conn = MySQLdb.connect (
  4. host = ' localhost ', user = ' root ',
  5. passwd = ", db = ' Test ', compress = 1,
  6. Cursorclass = MySQLdb.cursors.DictCursor, charset= ' UTF8 ') // <-Important
  7. cursor = Conn.cursor ()
  8. Cursor.execute ("Select name, txt from table")
  9. rows = Cursor.fetchall ()
  10. Cursor.close ()
  11. Conn.close ()
  12. For row in rows:
  13. Print row [' name '], row [' txt '] # bingo!

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

MYSQLDB Installation and use 2

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.