Python中pymysql基本使用

標籤：date   串連   set   mil   type   exe   插入   建立串連   資料庫操作   

 Python中pymysql模組通過擷取mysql資料庫命令列遊標執行資料庫命令來進行資料庫操作　　優點：操作資料庫語句所見即所得 (WYSIWYG)，執行了什麼資料庫語句都很清楚　　缺點：操作繁瑣，代碼量多

 

 

1. pymysql的基本使用

# -*- coding:utf-8 -*-# Author:Wong Duimport pymysql# 建立連結，相當於建立一個socketconn = pymysql.Connection(host=‘10.0.0.100‘, port=3306, user=‘root‘, passwd=‘123456‘, db=‘testdb‘)# 建立遊標，相當於進入 mysql> 命令操作介面cursor = conn.cursor()# 建表，和mysql命令列操作一樣try:    create_table = cursor.execute(‘‘‘create table student(                                  id int not null primary key auto_increment,                                  name char(32) not null,                                  register_date date not null DEFAULT "2018-05-09" );                                  ‘‘‘)except pymysql.err.InternalError as e:    # print(type(e))    print("\033[31;1m%s; Do nothing...\033[0m" %e)# 插入資料insert = cursor.execute(‘insert into student (name,register_date) values("junry", "2017-03-14");‘)insert2 = cursor.execute(‘insert into student (name,register_date) values("hongfa", "2015-03-14");‘)insert3 = cursor.execute(‘insert into student (name,register_date) values("jinglin", "2016-03-14");‘)# 查看錶資料select = cursor.execute(‘select * from student;‘)for line in cursor.fetchall():    print(line)# 修改表資料update = cursor.execute(‘update student set name="junwei" where id=1‘)select2 = cursor.execute(‘select * from student;‘)print(cursor.fetchone())# 刪除表資料delete = cursor.execute(‘delete from student;‘)select3 = cursor.execute(‘select * from student;‘)if cursor.fetchall():    print(cursor.fetchall())else:    print("This is a empty table...")# 提交conn.commit()cursor.close()  # 關閉遊標conn.close()    # 關閉串連# 等等 等等。。。

 

迴圈插入資料

# -*- coding:utf-8 -*-# Author:Wong Duimport pymysql# 建立串連conn = pymysql.Connect(host=‘10.0.0.100‘, port=3306, user=‘root‘, passwd=‘123456‘, db=‘testdb‘)# 建立遊標cursor = conn.cursor()# 迴圈插入列表many_list = [    (‘zhangsan‘, ‘2011-11-11‘),    (‘lisi‘, ‘2012-11-11‘),    (‘wangwu‘, ‘2022-10-09‘),]# 迴圈插入（插入多條內容）cursor.executemany("insert into student (name, register_date) VALUE(%s, %s);", many_list)# 修改遊標位置cursor.scroll(1, mode=‘relative‘)   # 相對移動，預設為relativecursor.scroll(1, mode=‘absolute‘)   # 絕對移動# fetchone()擷取一行資料、fetchmany(num)擷取指定行資料、fetchall()擷取所有行資料cursor.execute("select * from student;")for line in cursor.fetchall():    print(line)# 清楚student表的資料cursor.execute("delete from student;")# 提交conn.commit()cursor.close()  # 關閉遊標conn.close()    # 關閉串連

 

Python中pymysql基本使用