python3-pymysql模块

 创建数据库

import pymysql
db = pymysql.connect(host="192.168.146.209", user="root",password="123",port=3306)
cur = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "show databases;"

sql2 = 'create database pokemon2;'
cur.execute(sql)
results = cur.fetchall()
print(results)

cur.execute(sql2)

查看表 

import pymysql

#建立连接
conn = pymysql.connect(host='192.168.146.209',user='root',password='123',db='pikachu',charset='utf8')

#结果以字典的形式返回
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#结果以元祖的形式返回
cur = conn.cursor()

sql = 'show tables;'

cursor.execute(sql)
res1 = cursor.fetchall()

cur.execute(sql)
res2 = cur.fetchall()

print(res1)
print('----')
print(res2)

cursor.close()
cur.close()
conn.close()

 

#验证登陆
import pymysql
conn = pymysql.connect(host='192.168.146.209',user='root',password='123',db='pikachu',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = 'select * from info where name = "%s" and passwd = "%s";'%('cjk','123')

res = cursor.execute(sql)

print(res)

#如果res=1，代表sql执行成功；如果为0，代表执行不成功
#可以来做if判断

#sql = "GRANT ALL PRIVILEGES ON b.* TO 'b'@'localhost' IDENTIFIED BY 'b';"
#赋予权限时返回值为0（操作不同，返回值会有不同（具体情况博主不是很清楚））

 

创建表删除表

import pymysql
conn = pymysql.connect(host='192.168.146.209',user='root',password='123',db='pikachu',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'create table hahaha(id int,age int,high decimal(5,2));'
sql2 = 'drop table hahaha;'
cursor.execute(sql2)
cursor.close()
conn.close()

插入内容

import pymysql
conn = pymysql.connect(host='192.168.146.209',user='root',password='123',db='pikachu',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = "insert into info values('pikachu',123);"

cursor.execute(sql)

#注意提交事务(增删改都要提交事务)
conn.commit()


#插入多条数据
sql = 'insert into info values(%s,%s);'
data = [
    ('july', '147'),
    ('june', '258'),
    ('marin', '369')
]
# 拼接并执行sql语句
cursor.executemany(sql, data)