Python & amp; MySQL

Python & MySQL
Environment: windows8 + Python2.7 + MySQL5.6

I tried to embed the SQL language in C/C ++, and finally failed in its complicated "tedious" Environment configuration. Later I found that Python and MySQL are easier to implement, in Python, You can import the MySQLdb package and call the encapsulated API to embed SQL statements in Python to perform database operations;
Python calls the MySQLdb database function. First, it establishes a connection with the MySQL database and defines a cursor. Then it can transmit an SQL statement to the DBMS for execution, and then return the retrieval result through the API, pointed by the cursor pointer; in Python, you only need to use the cursor pointer to call the fetchall or fetchone method to access the retrieval result. In addition to retrieval, cursor can also perform many operations, such: create (view, table, database, etc.), add, delete, modify, Access Data Dictionary (describe table mode, display database, table, etc.); Submit MySQL or rollback, submit data or cancel operations on the database;

In addition to the tedious command line interface, MySQL now has a lot of user-friendly visual interface operations. I use navicat.

1. Import the MySQLdb package
Type import MySQLdb in the console. If no error message is displayed, You can import MySQLdb successfully.


2. Connect and disconnect the database

Conn = MySQLdb. connect (# create a connection named conn host = 'localhost', # host user = 'root', # local user passwd = '', # password db = 'mysql _ test_db ', # Name of the connected database) conn. close ()

3. Define the cursor and close the cursor.

cur = conn.cursor()cur.close()

Note: Of course, you can define multiple cursors.

4. Common cursor methods. Search is used as an example:

sql = "select * from students"cur.execute(sql)print cur.fetchall()

For SQL statements that comply with MySQL syntax rules, cur calls the execut method and passes the SQL statement to the DBMS for execution. The returned value is directed to cur;
The fetchall () method gets all rows, the fetchone () method gets one row, and the fetchevery (int I) method gets multiple rows (I rows );
The specific number of rows returned can be recorded by count = cur.exe cute (SQL;
Print result:

It seems that there is a problem with printing. The solution is provided later;

5. Simple SQL commands:
Database creation: create database database_name;
Create table table_name (
Attribute name data type [other description],
...);
Delete database: drop database database_name;
Drop table table_name;
Query table mode: describe table_name;
Insert: insert into table_name (column_name,...) values (...);
Query: select * | column_name,... from table_name where condition;
Update: update table_name set column_name =... where condition;
Delete: delete from table_name where condition;
Modify: alter table table_name [add | modify | drop unique];
In addition to the above operations:
Show databases;
Use database_name;
Close database_name;
Show tables;
Describe table_name;
Note: delete from table_name only deletes the data in the table, and the table mode is still in;

6. transaction commit
In order to ensure the atomicity of Database Operations (Several SQL operations constitute a transaction, such as transfer operations, one account must be reduced, and the other account must be increased so that normal operation can continue; for example, if the same time is the same as the departure and termination location, the same train will not buy the same seat) concurrency control, so after each transaction is completed, it will be checked for errors, whether to submit or cancel the operation;
Conn. commit () and conn. rollback ();

7. Error Detection:
You can use the error capture mechanism try... wait t ...;;

8. It is possible to use a string for parameter passing:

sql="select aid,sum(dollars) from orders where cid=%s group byaid"cur.execute(sql,[agent])

For example, you need to construct a complete SQL statement based on the entered agent value. Of course, it can also be multiple parameters:
Cur.exe cute (SQL, [s1, s2,...])

9. Output Encoding Problems:
Print cur. fetchall ()
Or
Print cur. fetchone ()
In this way, all the records or one record are equivalent to tuples. In this way, the output is MySQL character encoding. How can we display the utf8 encoding shown in the preceding example properly?
Print each component one by one:
Paste the print function:

Def connect_width (text, width): stext = text iftype (text) = int or type (text) = long: # if it is a numeric type, convert it to a numeric type stext = str (text) return "%-* s" % (width, stext) def Print_MySqldb_Result (cur): str_result = "|" row1 = "" array_lenth = [] iflen (cur. fetchall () = 0: print "cur is null, check your SQL select" else: for field_desc in cur. description: if len (field_desc [0])> field_desc [2]: array_lenth.append (len (field_desc [0]) else: array_lenth.append (field_desc [2]) text = connect_width (field_desc [0], field_desc [2]) str_result = str_result + text + "|" lenth = len (str_result) for I in range (lenth ): if I = 0: row1 = row1 + "+" else: if I = lenth-1: row1 = row1 + "+" else: row1 = row1 + "-" print row1 print str_result print row1 for I in cur: row2 = "|" k = 0 for j in I: text = connect_width (j, array_lenth [k]) row2 = row2 + text + "|" k = k + 1 print row2 print row1

Format the print effect:

If there is a problem with the display of Chinese characters or the combination of formatted and output Chinese and English, you can continue to find the corresponding solution,
It mainly solves the problem of text width, which is not provided here;

10. Python encoding and MySQL Encoding Problems:
Show variables like 'Char %'
Display result:

The default encoding is Latin1, so you need to modify it in the MySQL root directory my. ini file:
Default-character-set = utf8
Init_connect = 'set NAMES utf8'
Add the above two rows.

Complete Python code

ImportMySQLdb def connect_width (text, width): stext = text iftype (text) = int or type (text) = long: # If the numeric type needs to be converted to numeric type stext = str (text) return "%-* s" % (width, stext) def Print_MySqldb_Result (cur ): str_result = "|" row1 = "" array_lenth = [] iflen (cur. fetchall () = 0: print "cur is null, check your SQL select" else: for field_desc in cur. description: if len (field_desc [0])> field_desc [2]: array_lenth.append (len (field_desc [0]) else: array_lenth.append (field_desc [2]) text = connect_width (field_desc [0], field_desc [2]) str_result = str_result + text + "|" lenth = len (str_result) for I in range (lenth ): if I = 0: row1 = row1 + "+" else: if I = lenth-1: row1 = row1 + "+" else: row1 = row1 + "-" print row1 print str_result print row1 for I in cur: row2 = "|" k = 0 for j in I: text = connect_width (j, array_lenth [k]) row2 = row2 + text + "|" k = k + 1 print row2 print row1 # print "print OK" conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '', db = 'mysql _ test_db ', # charset = 'utf8',) cur = conn. cursor () while 1: agent = input ("Please input cid, input 1 to exit: \ n") ifagent = 1: break else: # agent = "C1" SQL = "select aid, sum (dollars) from orders where cid = % s group byaid" cur.exe cute (SQL, [agent]) print "agent_dollars with cid = '% S'" % agent Print_MySqldb_Result (cur) # print ('run OK') conn. commit () cur. close () conn. close ()

Running result: