Operation of Python MySQL (i)

The idea of using Python to manipulate MySQL:

1. Connection database: IP, port number, password, account, database

2. Creating Cursors

3. Execute SQL statements

4. Get execution Results

5. Close the cursor and close the connection

conn = Pymysql.connect (host= ' 211.149.218.16 ', user= ' jxz ', passwd= ' 123456 ', port=3306,db= ' jxz ', charset= ' UTF8 ')
# port number can not write character string, can only be int type, do not write, do not write is the default port number; CharSet cannot write ' Utf-8 ', can only write ' UTF8 '
cur = conn.cursor (cursor=pymysql.cursors.dictcursor) #建立游标, specifies that the cursor object is a dict type and can be an illusion that the cursor is a warehouse administrator
Cur.execute (' select * from Bt_stu limit 5 ') #执行sql语句
res = Cur.fetchall () #获取执行结果, which puts the results into a two-dimensional tuple, each of which is also a tuple
Print (RES)
Cur.close () #关闭游标
Conn.close () #关闭连接

Execution Result:

[{' Sex ': 1, ' type ': 1, ' ID ': 502, ' real_name ': ' Chou-Heung ', ' phone ': ' 18612341231 ', ' class ': ' Patron tun '},
{' Sex ': 1, ' type ': 1, ' ID ': 503, ' real_name ': ' Wang Lan ', ' phone ': ' 18723561789 ', ' class ': ' Scorpio 3 '},
{' Sex ': 1, ' type ': 1, ' ID ': 506, ' real_name ': ' Fix the fairy ', ' phone ': ' 18688866686 ', ' class ': ' Road Cut '},
{' Sex ': 1, ' type ': 1, ' ID ': 508, ' real_name ': ' Jia Meng margin ', ' phone ': ' 18612333231 ', ' class ': ' Patron tun '},
{' Sex ': 1, ' type ': 1, ' ID ': 511, ' real_name ': ' Ai-da ', ' phone ': ' 18332341231 ', ' class ': ' Patron tun '}]

Other fragmented knowledge points related to Pymysql:

# res = Cur.fetchall () #获取sql语句执行的结果, which puts the results in a two-dimensional tuple, each data is also a tuple
# res = Cur.fetchone () #只获取一条结果, the result is a one-dimensional tuple

# Cur.scroll (0,mode= ' absolute ') #移动游标, to the front
# cur.scroll (3,mode= ' relative ') #移动游标, to relative to the current position,-1 is forward, 1 is backward

Onn.commit () #提交, in addition to the query, additions and deletions need to be submitted

Operation of Python MySQL (i)