"Python" Learning note 5-working with MySQL database pymysql

Import Pymysql
#其他数据库, such as the Oracle module is pyoracle

#1, link database mysq IP port number password account database
#2, creating Cursors
#3, Execute SQL
#4, Get results
#5, close the connection, close the cursor


# 1, Show Table example

1 #conn = pymysql.connect (host = ' x.x.x.x ',2 #user = ' Jxz ', passwd = ' 123456 ',3 #port = 3306, db = ' jxz ', charset= ' UTF8 ') #charset must be UTF8 cannot be utf-84 #cur = conn.cursor () #建立游标, cursor you think it's a warehouse manager.5 ## Cur.execute (' Show Tables; ') #执行sql语句6 #cur.execute (' select * from Bt_stu limit 5; ') #执行sql语句7 ## Print (Cur.execute (' Show Tables; ')) #执行sql语句 return8 #print (Cur.fetchall ()) #获取sql语句执行的结果9 #res = Cur.fetchall () #获取sql语句执行的结果 (' Bt_stu ',), (' Hkk ',), (' Hkk2 ',), (' Jxz_stu ',), (' Products_nyy ',), (' Stu ',), (' U Ser ',), (' User_nyy ',), (' user_passwd ',), (' zmx ',))Ten #print (res[0][3]) One #cur.close () #关闭游标 A #conn.close () #关闭连接

# # 2, query example

#conn = pymysql.connect (host = ' x.x.x.x ',#user = ' Jxz ', passwd = ' 123456 ',#port = 3306, db = ' jxz ', charset= ' UTF8 ') #charset must be UTF8 cannot be utf-8#cur = conn.cursor () #建立游标, cursor you think it's a warehouse manager.#cur.execute (' select * from Bt_stu limit 2; ') #执行sql语句## res = Cur.fetchall () #获取sql语句执行的结果, put the result in a tuple (array), based on the result of the array, such as res[0][3]## (1, ' Jia Meng margin ', 1, ' 18612341231 ', ' Patron tun ', 1), (2, ' director ', 0, ' 19212345678 ', ' Patron tun ', 1), (4, ' director ', 0, ' 19312345678 ', ' Patron tun ', 1), (5, ' director ', 0, ' 19312345671 ', ' Patron tun ', 1), (6, ' ZDQ ', 0, ' 12312345678 ', ' Patron tun ', 1))## res = Cur.fetchall () #获取所有结果#res = Cur.fetchone () #只获取一条结#res = Cur.fetchone () #再获取剩下的 The first result # (2, ' director ', 0, ' 19212345678 ', ' Patron tun ', 1)#res = Cur.fetchall () #取所有剩下的#cur.scroll (0,mode= ' absolute ') #移动游标, to the front, #一般很少去移动游标#cur.scroll ( -10,mode= ' relative ') #移动游标, with respect to the current position,-X is moving forward x, X is moving backwards x#res = Cur.fetchone ()#Print (res)#cur.close () #关闭游标#conn.close () #关闭连接

# 3, insert

1 #Insert Update Delete executes these SQL and must be committed before it takes effect, Conn.commit2 #conn = pymysql.connect (host = ' x.x.x.x ',3 #user = ' Jxz ', passwd = ' 123456 ',4 #port = 3306, db = ' jxz ', charset= ' UTF8 ') #charset must be UTF8 cannot be utf-85 #cur = conn.cursor () #建立游标, cursor you think it's a warehouse manager.6 #sql = "INSERT into ' bt_stu ' (' real_name ', ' sex ', ' phone ', ' class ', ' type ') VALUES (' Cm1 ', ' 1 ', ' 15712341231 ', ' Patron tun ') , ' 1 '); "7 #cur.execute (SQL) #执行sql语句8 #Conn.commit ()9 #cur.close () #关闭游标Ten #conn.close () #关闭连接

# 4, cursor does not output tuples, output dictionary

1conn = Pymysql.connect (host ='x.x.x.x',2user ='Jxz', passwd ='123456',3Port = 3306, db ='Jxz', charset='UTF8')#CharSet must be UTF8 can't be utf-84cur = conn.cursor (cursor=pymysql.cursors.dictcursor)#Create cursors, cursors you think are warehouse administrators5sql ="select * from Bt_stu limit 2"6Cur.execute (SQL)#Execute SQL statement7 Print(Cur.fetchone ())#A statement that returns a dictionary8 #print (Cur.fetchall ()) #数组里面包含字典 [{' Phone ': ' 18612341231 ', ' ID ': 502, ' Sex ': 1,。。。。 9Cur.close ()#Close CursorsTenConn.close ()#Close Connection

"Python" Learning note 5-working with MySQL database pymysql