5, Eighth week-network programming Advanced-Python connection Mysql

There are two main ways in which Python operates MySQL:
Native module: pymsql where Pymsql is the module that operates MySQL in Python, using the same method as MySQLdb.
ORM Framework: Sqlachemy

Mysql pymysql Basic Steps

1. PYMYSQL basic structure syntax is as follows:

Import pymysql# Create Connection conn = Pymysql.connect (host= ' 10.10.28.5 ', port=3306,user= ' Chen ', passwd= ' Chen1203 ... ', db= ' school ' , charset= "UTF8") #创建游标cursor = Conn.cursor () #创建当前数据的游标 # Execute SQL and return the number of rows affected, general Database Operations Command: Select, UPDATE, insert# Cursor.execute ("INSERT into a (number) VALUES (1203)") #执行SQL and returns the number of rows affected, performing multiple cursor.executemany ("insert into a VALUES (%s) ", [(" ""), ("1001"), ("1002")]) cursor.execute ("select * from a  ") #output = Cursor.fetchmany (3) # Based on the return of the Execute SQL, read the first three digits #output = Cursor.fetchone ()   #根据执行SQL的返回, read a number output = Cursor.fetchall ()    #根据执行SQL的返回, Print #提交 is printed, or you cannot save the new or modified data conn.commit () #关闭游标cursor. Close () #关闭连接conn. Close () Output: ((1203,), (1203,), ( 1203,), (1203,), (1000,), (1001,), (1002,))

Note: In the presence of Chinese, the connection needs to add charset= ' UTF8 ', otherwise the Chinese display garbled.

2. Get the self-increment ID number of new data

Import pymysql# Create Connection conn = Pymysql.connect (host= ' 103.66.55.234 ', port=3306,user= ' Chen ', passwd= ' Chen1203 ... ', db= ' School ', charset= "UTF8") #创建游标cursor = Conn.cursor () #创建当前数据的游标 # Execute SQL and return the number of rows affected, general Database Operations Command: Select, UPDATE, insert# Cursor.execute ("INSERT into a (number) VALUES (1203)") #执行SQL and returns the number of rows affected, performing multiple cursor.executemany ("insert into Chen (name) VALUES (%s) ", [(" Qing4 "), (" Qing5 "), (" Qing6 ")]) #提交, or you cannot save a new or modified data conn.commit () #关闭游标cursor. Close () #关闭连接conn. Close ( ) new_id = Cursor.lastrowid #获取自增idprint (new_id)

3. Moving the cursor

The operation is done by cursors, and the control of the cursor is also necessary.

Note: In order to fetch data, you can use Cursor.scroll (Num,mode) to move the cursor position, such as: Cursor.scroll (1,mode= ' relative ') # move Cursor.scroll relative to the current position ( 2,mode= ' absolute ') # relative absolute position movement

4. Data type

About the data obtained by default is the Ganso type, if you want or the dictionary type of data

Import pymysql# Create Connection conn = Pymysql.connect (host= ' 103.66.53.98 ', port=3306,user= ' Chen ', passwd= ' Chen1203 ... ', db= ' School ', charset= "UTF8") #设置游标为字典类型cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor) cursor.execute ("SELECT * From Chen ") row = Cursor.fetchall () print (row) #提交conn. Commit () #关闭游标cursor. Close () #关闭连接conn. Close ()

5. Call the stored procedure

A, no parameter stored procedure

Import Pymysql conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ') #游标设置为字典类型cursor = Conn.cursor (Cursor=pymysql.cursors.dictcursor) #无参数存储过程cursor. Callproc (' p2 ')  #等价于cursor. Execute ("Call P2 ()") Row_1 = Cursor.fetchone () print Row_1 conn.commit () cursor.close () Conn.close ()

B. Stored Procedures with parameters

Import Pymysql conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ') cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor) cursor.callproc (' P1 ', args= (1, 3, 4)) #获取执行完存储的参数, Parameter @ begins with Cursor.execute ("Select @p1, @_p1_1,@_p1_2,@_p1_3")  #{u ' @_p1_1 ': $, U ' @p1 ': None, U ' @_p1_2 ': 103, U ' @_p1_3 ': 24}row_1 = Cursor.fetchone () print row_1conn.commit () cursor.close () Conn.close ()

6. Using with simplifies the connection process

Simplify the connection process with context management

Import Pymysqlimport contextlib# Defines the context manager, automatically closes the connection after connection @contextlib.contextmanagerdef MySQL (host= ' 127.0.0.1 ', port=3306, User= ' root ', passwd= ', db= ' tkq1 ', charset= ' UTF8 '):  conn = Pymysql.connect (Host=host, Port=port, User=user, passwd =PASSWD, Db=db, charset=charset)  cursor = conn.cursor (cursor=pymysql.cursors.dictcursor)  try:    yield Cursor  finally:    conn.commit ()    cursor.close ()    conn.close () # execution Sqlwith MySQL () as cursor:  print ( cursor)  Row_count = Cursor.execute ("SELECT * from Tb7")  row_1 = Cursor.fetchone ()  print Row_count, row_1

pymysql anti-injection: reference link https://www.cnblogs.com/wt11/p/6141225.html

5, Eighth week-network programming Advanced-Python connection Mysql