mysql python connector_mysql-connector-python 学习笔记

#!/usr/bin/env python3

# -*- coding: utf-8 -*-

########## prepare ##########

# install mysql-connector-python:

# pip install mysql-connector-python

import mysql.connector

class DBMysql():

"""mysql数据库相关操作"""

def __init__(self,ip="127.0.0.1",port = "3306",user="root", password="123456", database="test"):

self.ip = ip

self.port = port

self.user = user

self.pwd = password

self.db = database

def createOperate(self,createSql):

"""创建数据库table"""

conn = mysql.connector.connect(user=self.user, password=self.pwd, database=self.db)

cursor = conn.cursor()

try:

# 创建user表:

cursor.execute(createSql)

# 提交事务:

conn.commit()

except:

print("创建表异常")

pass

finally:

# 关闭Cursor和Connection:

cursor.close()

conn.close()

def insertOperate(self,insertSql):

"""插入数据库操作"""

conn = mysql.connector.connect(user=self.user, password=self.pwd, database=self.db)

cursor = conn.cursor()

try:

# 插入一行记录，注意MySQL的占位符是%s:

cursor.execute(insertSql)

print('rowcount =', cursor.rowcount)

# 提交事务:

conn.commit()

except:

print("数据插入异常")

pass

finally:

# 关闭Cursor和Connection:

cursor.close()

conn.close()

def queryOperate(self,querySql):

"""查询数据库操作"""

conn = mysql.connector.connect(user=self.user, password=self.pwd, database=self.db)

cursor = conn.cursor()

try:

cursor.execute(querySql)

values = cursor.fetchall()

print(values)

except:

print("查询数据异常")

pass

finally:

# 关闭Cursor和Connection:

cursor.close()

conn.close()

if __name__ == '__main__':

dbm = DBMysql(ip="127.0.0.1",port = "3306",user="root", password="******", database="test")

createSql = 'create table user (id varchar(20) primary key, name varchar(20));'

insertSql = 'insert into user (id, name) values ("1", "Michael");'

querySql = 'select * from user;'

# querySql = 'select * from user where id = "1";'

# dbm.createOperate(createSql)

# dbm.insertOperate(insertSql)

dbm.queryOperate(querySql)

# names = ["zhangSan","liSi","wangWu","zhaoLiu"]

# i = 1

# for name in names:

# i = i + 1

# insertSql = ''

# insertSql = 'insert into user (id, name) values("'+str(i)+'","'+name+'")'

# dbm.insertOperate(insertSql)