如何使用Python玩MySQL資料庫

一、背景

我是在Anaconda notebook中進行連接實驗的，環境Python3.6，當然也可以在Python Shell裡面進行操作。

最常使用也最穩定的用來連接MySQL資料庫的python函式庫是PyMySQL。

二、基本操作

1、安裝PyMySQL函式庫

最簡單的方式：

在命令列輸入pip install pymysql

或：

下載whl檔案[1]進行安裝，安裝過程自行百度。

2、安裝MySQL資料庫

MySQL資料庫有兩種：

MySQL和MariaDB

#我用的是MariaDB，它是MySQL的一個分支。

兩者在絕大部分效能上是相容的，使用起來感覺不到啥差別。

給出下載位址：

MySQL[2]

，
• MariaDB[3]

  ，安裝過程很簡單，一路Next Step，但要寫好密碼。 有個小插曲，MySQL和MariaDB相當於姐姐妹妹的關係，兩者由同一個人（Widenius）創建的。

MySQL被Oracle收購後，Widenius先生覺得不爽，於是搞了個MariaDB，可以完全取代MySQL。

• 大牛就是任性。 3、SQL基本語法

下面要用SQL的表格建立、查詢、資料插入等功能，這裡簡單介紹一下SQL語言的基本語句。

• 檢視資料庫：

  SHOW DATABASES;

• 建立資料庫：

  CREATE DATEBASE 資料庫名稱;

• 使用資料庫：

  USE 資料庫名稱;

• 檢視資料表：

  SHOW TABLES;

• 建立資料表：

  CREATE TABLE 資料表名稱（列名1（資料型別1），列名2 （資料型別2））；

• 插入資料：

  INSERT INTO 表格名稱（列名1，列名2） VALUES（資料1，資料2）；

查看資料：

SELECT * FROM 資料表名稱；

更新資料：

UPDATE 資料表名稱SET 欄位名稱1=新資料1，資料列名稱2=新資料2 WHERE 某列=某資料；

4、連接資料庫

安裝好必要得到檔案與函式庫後，接下來正式開始連線資料庫吧，雖然神秘感卻不難喔！

#首先导入PyMySQL库
import pymysql
#连接数据库，创建连接对象connection
#连接对象作用是：连接数据库、发送数据库信息、处理回滚操作（查询中断时，数据库回到最初状态）、创建新的光标对象
connection = pymysql.connect(host = 'localhost' #host属性
                            user = 'root' #用户名
                            password = '******'  #此处填登录数据库的密码
                            db = 'mysql' #数据库名
                            )

執行這段程式碼就連結好了！

5、增刪改查操作

首先來查看一下有哪些資料庫：

#创建光标对象，一个连接可以有很多光标，一个光标跟踪一种数据状态。
#光标对象作用是：、创建、删除、写入、查询等等
cur = connection.cursor()
#查看有哪些数据库，通过cur.fetchall()获取查询所有结果
print(cur.fetchall())

列印出所有資料庫：

(('information_schema',),
('law',),
('mysql',),
('performance_schema',),
('test',))

在test資料庫裡創建表:

#使用数据库test
cur.execute('USE test')
#在test数据库里创建表student，有name列和age列
cur.execute('CREATE TABLE student(name VARCHAR(20),age TINYINT(3))')

向資料表student中插入一條資料：

sql = 'INSERT INTO student (name,age) VALUES (%s,%s)'
cur.execute(sql,('XiaoMing',23))

檢視資料表student內容：

cur.execute('SELECT * FROM student')
print(cur.fetchone())

列印輸出為：('XiaoMing', 23)

Bingo！是我們剛剛插入的一條資料

最後，要記得關閉遊標和連接：

#关闭连接对象，否则会导致连接泄漏，消耗数据库资源
connection.close()
#关闭光标
cur.close()

OK了，整個流程大致如此。

當然這裡都是很基礎的操作，更多的使用方法需要在PyMySQL官方文件[4]

裡去尋找。

三、導入大數據檔

以csv檔為例，csv檔導入資料庫一般有兩種方法：######1、透過SQL的insert方法一一導入，適合資料量小的CSV文件，這裡不做贅述。 ######２、透過load data方法導入，速度快，適合大數據文件，也是本文的重點。 ######樣本CSV檔案如下：###############整體工作分為3步驟：######1、用python連接mysql資料庫；###

2、基于CSV文件表格字段创建表；

3、使用load data方法导入CSV文件内容。

sql的load data语法简介：

LOAD DATA LOCAL INFILE 'csv_file_path' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES

csv_file_path 指文件绝对路径 table_name指表名称 FIELDS TERMINATED BY ','指以逗号分隔 LINES TERMINATED BY '\\r\\n'指换行 IGNORE 1 LINES指跳过第一行，因为第一行是表的字段名

下面给出全部代码：

#导入pymysql方法
import pymysql


#连接数据库
config = {:'',
         :3306,
         :'username',
         :'password',
         :'utf8mb4',
         :1
         }
conn = pymysql.connect(**config)
cur = conn.cursor()


#load_csv函数，参数分别为csv文件路径，表名称，数据库名称
def load_csv(csv_file_path,table_name,database='evdata'):
   #打开csv文件
   file = open(csv_file_path, 'r',encoding='utf-8')
   #读取csv文件第一行字段名，创建表
   reader = file.readline()
   b = reader.split(',')
   colum = ''
   for a in b:
       colum = colum + a + ' varchar(255),'
   colum = colum[:-1]
   #编写sql，create_sql负责创建表，data_sql负责导入数据
   create_sql = 'create table if not exists ' + table_name + ' ' + '(' + colum + ')' + ' DEFAULT CHARSET=utf8'
   data_sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES" % (csv_filename,table_name)

   #使用数据库
   cur.execute('use %s' % database)
   #设置编码格式
   cur.execute('SET NAMES utf8;')
   cur.execute('SET character_set_connection=utf8;')
   #执行create_sql，创建表
   cur.execute(create_sql)
   #执行data_sql，导入数据
   cur.execute(data_sql)
   conn.commit()
   #关闭连接
   conn.close()
   cur.close()

以上是如何使用Python玩MySQL資料庫的詳細內容。更多資訊請關注PHP中文網其他相關文章！