Environment: windows8+python2.7+mysql5.6
Having tried to embed the SQL language in C + +, and eventually failed in its complex "cumbersome" environment configuration, it was found that Python and MySQL were easier to implement and could import mysqldb packages in Python. You can embed SQL statements in Python by invoking an API interface that is already encapsulated. Implement the operation of the database;
Python calls the MySQLdb library function, first establishing and MySQL database connection connection. Defines a cursor for cursors. will be able to transmit a SQL statement to the DBMS and run it over to the DBMS. The search results are then returned through the API. Pointed by the cursor pointer; in Python. It is only necessary to use the cursor pointer to call the Fetchall or Fetchone method to access the results of the search, in addition to retrieving, the cursor can also complete a lot of operations. For example: New (views, tables, databases, etc.). Delete and change, access to data dictionary (descriptive narrative table mode. display databases, tables, etc.); then submit MySQL or rollback, submit the data or revoke the operation of the database;
In addition to the cumbersome command-line interface, MySQL now has a lot of user-friendly visual interface operation. I'm using Navicat.
1. Import MYSQLDB Package
Type import MySQLdb in the console, assuming there are no error prompts, to successfully import the MYSQLDB
2. Connect and Disconnect the database
conn = MySQLdb.connect (#建立一个连接. Named conn
host = ' localhost ', #主机
user = ' root ', #本地用户
passwd = ', #password
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. Cursors are frequently used. Take the search as an example:
sql = "SELECT * from students"
cur.execute (SQL)
print cur.fetchall ()
SQL statements that conform to the MySQL syntax rules, cur call the Execut method. The SQL statement is passed to the DBMS and the return value is pointed to by cur;
The Fetchall () method gets all the rows. The Fetchone () method gets a row, and the fetchmany (int i) method gets multiple rows (i rows);
Verbose returns how many rows can be logged by count = Cur.execute (SQL).
Printing results:
There seems to be a problem with printing, and the workaround is given later.
5. Simple SQL commands are listed:
Build Library: CREATE DATABASE database_name;
Build tables: CREATE TABLE table_name (
Property name data type [other descriptive narrative],
...);
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;
Change: 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 schema of the table is still there.
6. Transaction Submission
In order to ensure the atomicity of the database operations (a number of SQL operations comprise a transaction, such as a transfer operation, one account must be reduced, the other account increased, so that the ability to operate normally;) concurrency control, for example, if the same train will buy the same seat at the same time as the departure stop. So after each transaction operation is complete. Whether it should be committed or revoked if the error is detected.
Conn.commit () and Conn.rollback () respectively;
7. Error Detection:
To go deep. Ability to use error trapping mechanism try...except ...;
8. Possible use of string to refer to the problem:
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 ()
All records obtained in this way are either a record or a tuple, which is printed directly. The output is the MySQL character encoding, the above example shows the UTF8 encoding, then how to display normal?
Print each of the components individually:
Paste the Print function:
def connect_width (text, width): stext =text iftype (text) ==int or type (text) ==long: #假设是数字型须要转为字符型 stext = St R (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.appe nd (len (field_desc[0))) Else:array_lenth.append (field_desc[2]) text=connect_width (fie
LD_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+ "-" PR int 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 Row1Format the print effect:
Suppose there is a problem displaying Chinese. Or the formatted output has problems with the combination of English and Chinese. Can continue to find the corresponding method to solve.
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. This needs to go to the MySQL root folder change My.ini file changes:
Default-character-set=utf8
init_connect= ' SET NAMES UTF8 '
Join the two lines. 'll be
With full Python code attached
Importmysqldb def connect_width (text, width): stext =text iftype (text) ==int or type (text) ==long: #假设是数字型须要转为字 Character 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.appe nd (len (field_desc[0))) Else:array_lenth.append (field_desc[2]) text=connect_width (fie
LD_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+ "-" PR
int 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= "Selec T aid,sum (dollars) from 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 ()Execution Result:
python& Mysql
