Use Python to operate Mysql, and use Python to operate 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
12. # Establish a connection to the Database System
13. Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ')
15. # Retrieve operation cursor
16. Cursor = conn. cursor ()
17. # Execute SQL to create a database.
18. Cursor.exe cute ("" create database python """)
20. # Close the connection and release resources
21. 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
12. # Establish a connection to the Database System
13. Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ')
15. # Retrieve operation cursor
16. Cursor = conn. cursor ()
17. # Execute SQL to create a database.
18. Cursor.exe cute ("" create database if not exists python """)
20. # Selecting a database
21. Conn. select_db ('python ');
22. # Execute SQL to create a data table.
23. Cursor.exe cute ("" create table test (id int, info varchar (100 ))""")
25. Value = [1, "inserted? "];
27. # Insert a record
28. Cursor.exe cute ("insert into test values (% s, % s)", value );
30. Values = []
33. # Generating insert parameter values
34. For I in range (20 ):
35. Values. append (I, 'Hello mysqldb, I am recoder '+ str (I )))
36. # Insert multiple records
38. Cursor.exe cute.pdf ("" insert into test values (% s, % s) ", values );
40. # Close the connection and release resources
41. 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. #######################################
14. Import MySQLdb
16. Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ', db = 'python ')
18. Cursor = conn. cursor ()
20. Count = cursor.exe cute ('select * from test ')
22. Print 'total % s record records ', count
24. # Obtain a record. Each record is returned as a tuples.
25. Print "get only one record :"
26. Result = cursor. fetchone ();
27. Print result
28. # Print 'id: % s info: % s' % (result [0], result [1])
29. Print 'id: % s info: % s' % result
31. # 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.
32. Print "get only five records :"
33. Results = cursor. fetchtasks (5)
34. For r in results:
35. Print r
37. Print "get all results :"
38. # Reset the cursor position. The value 0 indicates the offset. mode = absolute | relative. The default value is relative,
39. Cursor. scroll (0, mode = 'absolute ')
40. # Getting all results
41. Results = cursor. fetchall ()
42. For r in results:
43. Print r
44. 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