创建数据库
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)