Use Python to operate Mysql, and use Python to operate Mysql

Source: Internet
Author: User
Tags python mysql

Use Python to operate Mysql, and use Python to operate Mysql

The main programming language is Java, and Mysql is also used during development. for testing purposes, you often need to operate databases, such as backup, insert test data, and modify test data, in some cases, you cannot simply use SQL to complete the task or complete the task well. It is a little too troublesome to write in Java, so you can think of Python. The Python syntax is concise and does not need to be compiled. You can complete the task well. Today, I checked Python's operations on Mysql and made a record.

 

First of all, the environment required for installation is not mentioned in Mysql and Python.

The main installation of MySQLdb, you can go to sf.net download, the specific address is the http://sourceforge.net/projects/mysql-python/

If Ubuntu is used

 

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 # case sensitive !!

If no error is reported, the installation is successful and may continue.


MySQLdb is equivalent to the JDBC Driver of MySQL in JAVA in Python. Python also has similar data interface specifications for Python db api. MySQLdb is the implementation of Mysql. The operation is also relatively simple, just like operating databases on other platforms or languages, that is, establishing a connection with the database system, then inputting SQL to the database, and then obtaining results from the database.

First, write the simplest one to create a database:

 

Python code
  1. #! /Usr/bin/env python
  2. # Coding = UTF-8
  3. ###################################
  4. # @ Author migle
  5. # @ Date 2010-01-17
  6. ##################################
  7. # MySQLdb example
  8. #
  9. ##################################
  10. Import MySQLdb
  11. # Establish a connection to the Database System
  12. Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ')
  13. # Retrieve operation cursor
  14. Cursor = conn. cursor ()
  15. # Execute SQL to create a database.
  16. Cursor.exe cute ("" create database python """)
  17. # Close the connection and release resources
  18. Cursor. close ();

 

 

Create databases, create tables, insert data, and insert multiple data records

 

Python code
  1. #! /Usr/bin/env python
  2. # Coding = UTF-8
  3. ###################################
  4. # @ Author migle
  5. # @ Date 2010-01-17
  6. ##################################
  7. # MySQLdb example
  8. #
  9. ##################################
  10. Import MySQLdb
  11. # Establish a connection to the Database System
  12. Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ')
  13. # Retrieve operation cursor
  14. Cursor = conn. cursor ()
  15. # Execute SQL to create a database.
  16. Cursor.exe cute ("" create database if not exists python """)
  17. # Selecting a database
  18. Conn. select_db ('python ');
  19. # Execute SQL to create a data table.
  20. Cursor.exe cute ("" create table test (id int, info varchar (100 ))""")
  21. Value = [1, "inserted? "];
  22. # Insert a record
  23. Cursor.exe cute ("insert into test values (% s, % s)", value );
  24. Values = []
  25. # Generating insert parameter values
  26. For I in range (20 ):
  27. Values. append (I, 'Hello mysqldb, I am recoder '+ str (I )))
  28. # Insert multiple records
  29. Cursor.exe cute.pdf ("" insert into test values (% s, % s) ", values );
  30. # Close the connection and release resources
  31. Cursor. close ();

 

 

The query process is similar to the insert process, but there is only one more step to get the query result.

 

Python code
    1. #! /Usr/bin/env python
    2. # Coding = UTF-8
    3. ######################################
    4. #
    5. # @ Author migle
    6. # @ Date 2010-01-17
    7. #
    8. ######################################
    9. #
    10. # MySQLdb Query
    11. #
    12. #######################################
    13. Import MySQLdb
    14. Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ', db = 'python ')
    15. Cursor = conn. cursor ()
    16. Count = cursor.exe cute ('select * from test ')
    17. Print 'total % s record records ', count
    18. # Obtain a record. Each record is returned as a tuples.
    19. Print "get only one record :"
    20. Result = cursor. fetchone ();
    21. Print result
    22. # Print 'id: % s info: % s' % (result [0], result [1])
    23. Print 'id: % s info: % s' % result
    24. # Obtain five records. Note that the cursor has pointed to the second record because fetchone () was previously executed, that is, all records starting from the second record.
    25. Print "get only five records :"
    26. Results = cursor. fetchtasks (5)
    27. For r in results:
    28. Print r
    29. Print "get all results :"
    30. # Reset the cursor position. The value 0 indicates the offset. mode = absolute | relative. The default value is relative,
    31. Cursor. scroll (0, mode = 'absolute ')
    32. # Getting all results
    33. Results = cursor. fetchall ()
    34. For r in results:
    35. Print r
    36. Conn. close ()

When you use python to operate MySQL, the data displayed on the python interface is changed, but the data is not changed when you access the View table from MySQL.

If you are using a database operated by MySQLdb, You need to commit after executing the SQL statement. Example:
Conn = MySQLdb. connect (user = 'xxx', db = 'xxx', passwd = 'xxx', host = '123. 0.0.1 ', use_unicode = True, charset = 'utf8 ')
Cur = conn. cursor ()
Cur.exe cute ('Update table set xxx = XXX ')
Conn. commit ()
Cur. close ()
Conn. close ()

Python mysql Operation Problems

Dict1 = self. fuc1 ()
Dict2 = self. fun2 ()
Def newdict (self ):
Dict = {}
For k, v in dict1.items ():
Dict [k] = v
For k1, v1 in dict2.items ():
Dict [k1] = v1
Return dict

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.