python3安装pymysql_Python3.X 与 PyMysql

1.没有安装PyMysql,进行安装

pip3 install PyMysql

2.连接数据库

#! /usr/bin/python

# -*-coding:UTF-8-*-

import pymysql

def db_connect():

try:

# 打开数据库连接

db = pymysql.connect("localhost", "root", "123456", "test")

print('Database connection success')

# 关闭数据库连接

db.close()

except Exception as e:

print("Database connection failure.error：%s"% e)

def main():

db_connect()

if __name__ == "__main__":

main()

3.创建表

#! /usr/bin/python

# -*-coding:UTF-8-*-

import pymysql

def create_table():

# 打开数据库连接

db = pymysql.connect(host="localhost", port=3306, user="root", passwd="123456", db="test", charset='utf8')

# 使用cursor()方法创建一个游标对象cursor

cursor = db.cursor()

# 使用execute()方法执行SQL查询

cursor.execute("DROP TABLE IF EXISTS member")

# 使用预处理语句创建表

sql = """CREATE TABLE member(

`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

`nickname` char(20) NOT NULL COMMENT '昵称',

`realname` char(20) NOT NULL COMMENT '真实姓名',

`birth` datetime DEFAULT NULL COMMENT '出生年月日',

`hobby` varchar(255) NOT NULL COMMENT '爱好',

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`edit_time` datetime DEFAULT NULL COMMENT '编辑时间',

PRIMARY KEY(`id`),

KEY `nickname` (`nickname`),

KEY `realname` (`realname`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='会员表'"""

try:

cursor.execute(sql)

print("CREATE TABLE SUCCESS.")

except Exception as e:

print("CREATE TABLE FAILED,CASE:%S",e)

finally:

# 关闭数据库

db.close()

def main():

create_table()

4.批量插入数据

#! /usr/bin/python

# -*-coding:UTF-8-*-

import pymysql

def insert():

# 打开数据库连接

db = pymysql.connect(host="localhost", port=3306, user="root", passwd="123456", db="test", charset='utf8')

# 使用cursor()方法创建一个游标对象cursor

cursor = db.cursor()

data =  [

['二哥', '王力宏', '1992-01-01 08:00:00','吉他'],

['春哥', '陈小春', '1990-01-01 08:00:00', '古惑仔'],

['颖宝' ,'赵丽颖', '1995-01-01 08:00:00', '武侠剧']

]

# SQL 插入语句

sql = """INSERT INTO member(`nickname`,`realname`, `birth`, `hobby`) VALUES(%s, %s, %s, %s)"""

try:

# 批量插入数据，比循环插入快

cursor.executemany(sql,data)

# 数据提交

db.commit()

print("INSERT DATA SUCCESS.")

except Exception as e:

print("INSERT DATA FAILED,CASE:%S",e)

# 数据回滚

db.rollback()

finally:

# 关闭数据库

db.close()

def main():

insert()

if __name__ == "__main__":

main()

5.数据查询

#! /usr/bin/python

# -*-coding:UTF-8-*-

import pymysql

def select():

# 打开数据库连接

db = pymysql.connect(host="localhost", port=3306, user="root", passwd="123456", db="test", charset='utf8')

# 使用cursor()方法创建一个游标对象cursor

cursor = db.cursor()

# SQL 查询语句

sql = """SELECT * FROM member WHERE birth > %s"""

try:

# 执行SQL语句

cursor.execute(sql,('1990-01-01 08:00:00'))

# 获取所有记录列表

results = cursor.fetchall()

print( results )

return

for index, value in enumerate(results):

print("昵称：%s  真实姓名：%s  生日：%s \r\n" % ( value[1], value[2], value[3] ))

except Exception as e:

print("SELECT DATA FAILED,ERROR:%S",e)

finally:

# 关闭数据库

db.close()

def main():

select()

if __name__ == "__main__":

main()

6.数据更改

#! /usr/bin/python

# -*-coding:UTF-8-*-

import pymysql

import datetime

def update():

# 打开数据库连接

db = pymysql.connect(host="localhost", port=3306, user="root", passwd="123456", db="test", charset='utf8')

# 使用cursor()方法创建一个游标对象cursor

cursor = db.cursor()

# SQL 查询语句

sql = """UPDATE member SET realname = %s , edit_time = %s  WHERE id = %s"""

try:

# 执行SQL语句

result = cursor.execute(sql,("王小宏", datetime.datetime.now(), 1))

if result == 1:

# 数据提交

db.commit()

print("UPDATE DATA SUCCESS")

else:

# 抛出异常

raise NameError('ValueError')

except Exception as e:

# 数据回滚

db.rollback()

print("UPDATE DATA FAILED,ERROR:%S",e)

finally:

# 关闭数据库

db.close()

def main():

update()

if __name__ == "__main__":

main()

7.删除数据

#! /usr/bin/python

# -*-coding:UTF-8-*-

import pymysql

import datetime

def update():

# 打开数据库连接

db = pymysql.connect(host="localhost", port=3306, user="root", passwd="123456", db="test", charset='utf8')

# 使用cursor()方法创建一个游标对象cursor

cursor = db.cursor()

# SQL 查询语句

sql = """DELETE FROM member WHERE id = %s"""

try:

# 执行SQL语句

result = cursor.execute(sql,(1))

if result == 1:

# 数据提交

db.commit()

print("DELETE DATA SUCCESS")

else:

# 抛出异常

raise NameError('ValueError')

except Exception as e:

# 数据回滚

db.rollback()

print("DELETE DATA FAILED,ERROR:%S",e)

finally:

# 关闭数据库

db.close()

def main():

update()

if __name__ == "__main__":

main()