Environment: windows8+python2.7+mysql5.6
After trying to embed the SQL language in C + +, and finally lose the array in its complex "cumbersome" environment configuration, it was found that Python and MySQL are easier to implement, and in Python you can import the MYSQLDB package and call the API interfaces already packaged. You can embed SQL statements in Python to implement the operation of the database;
Python calls the MySQLdb library function, first establish and MySQL database connection connection, define a cursor cursor, you can transfer to the DBMS of SQL statements, to the DBMS execution, and then through the API to return the results of the search, Directed by the cursor pointer; in Python, you only need to call the Fetchall or Fetchone method with the cursor pointer to access the results, and in addition to the search, the cursor can do many things, such as new (views, tables, databases, etc.), Adding and deleting, accessing the data dictionary (describing the table mode, displaying the database, tables, etc.), then submitting the MySQL or rollback, submitting the data or revoking the operation of the database;
MySQL In addition to the cumbersome command line interface, now has a lot of user-friendly visual interface operation, I use Navicat
1. Import MYSQLDB Package
Type import MySQLdb in the console, and if you do not have an error message, you can import the MYSQLDB successfully
2. Connect and Disconnect the database
conn = MySQLdb.connect (#建立一个连接, named conn host = ' localhost ', #主机 user = ' root ', #本地用户 passwd = ', #密码 db = ' mysql_test_db ', #连接数据库名 ) conn.close ()
3. Defining the cursor cursor and closing the cursor
cur = conn.cursor () cur.close ()
Note: Of course, cursors can define multiple
4. Common methods for cursors, as an example of retrieval:
sql = "SELECT * FROM Students" Cur.execute (SQL) print Cur.fetchall ()
SQL statements that conform to the MySQL syntax rules, cur calls the Execut method, passes the SQL statement to the DBMS, and the return value is pointed to by cur;
The Fetchall () method gets all rows, the Fetchone () method gets a row, and the fetchmany (int i) method gets multiple rows (i rows);
The exact number of rows returned can be recorded by Count = Cur.execute (SQL);
Printing results:
Find printing seems to have a problem, the solution is given in the back;
5. Simple SQL commands are listed:
Build Library: CREATE DATABASE database_name;
Build tables: CREATE TABLE table_name (
Property name Data type [other description],
...);
Delete Library: drop database database_name;
Delete tables: 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;
UPDATED: UPDATE table_name set column_name= ... where condition;
Delete: Delete from table_name where condition;
Modified: ALTER TABLE TABLE_NAME [Add|modify|drop unique];
In addition to the above operations, there are:
show databases;
Use database_name;
Close database_name;
Show tables;
Describe table_name;
NOTE: Delete from table_name only deletes the data from the table, and the mode of the table is still there;
6. Transaction Submission
To ensure the atomicity of the database operation (a number of SQL operations consist of a transaction, such as a class transfer operation, one account must be reduced and the other account increased so that it can operate normally;) concurrency control, for example, if the same train will buy the same seat at the same time as the departure point Therefore, after each transaction operation completes, the error detection, whether should commit or revoke the operation;
Conn.commit () and Conn.rollback () respectively;
7. Error Detection:
To drill down, you can use the error trapping mechanism try...except ...; ；
8. Possible use of a string argument:
Sql= "Select Aid,sum (dollars) from orders where cid=%s group Byaid" Cur.execute (Sql,[agent])
For example, you need to make a complete SQL statement based on the input agent value, and of course multiple parameters:
Cur.execute (sql,[s1,s2,...])
9. Output encoding Problem:
Print Cur.fetchall ()
Or
Print Cur.fetchone ()
This gets all the records or is a record, the equivalent of a tuple, so direct printing, the output is the MySQL character encoding, the above example shows the UTF8 encoding, then how to display normal?
Print each of these components individually:
Paste the Print function:
def connect_width (text, width): stext =text iftype (text) ==int or type (text) ==long: #如果是数字型需要转为字符型 stext = str (t EXT) 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:f Or Field_desc in Cur.description:if len (field_desc[0]) >field_desc[2]: Array_lenth.append (Len (f Ield_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, or the format output of the English combination of problems, you can continue to find the appropriate method to resolve,
Mainly to solve the problem of the width of the text, here is not given;
10.Python encoding and MySQL coding issues:
Show variables like ' char% '
Show Results:
The default encoding is Latin1, which requires a change to the root directory of MySQL to modify the My.ini file:
Default-character-set=utf8
init_connect= ' SET NAMES UTF8 '
Add these two lines and you can
With full Python code attached
Importmysqldb def connect_width (text, width): stext =text iftype (text) ==int or type (text) ==long: #如果是数字型需要转为字符型 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:f Or Field_desc in Cur.description:if len (field_desc[0]) >field_desc[2]: Array_lenth.append (Len (f Ield_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 I Nput cid,input 1 to exit:\n ") Ifagent==1:break else: #agent =" C1 "sql=" select Aid,sum (dollars) F Rom orders where cid=%s group Byaid "Cur.execute (sql,[agent]) print" Agent_dollars with cid= '%s ' "%agent Print_mysqldb_result (cur) #print (' Run OK ') Conn.commit () Cur.close () Conn.close ()Operation Result:
Python&mysql
