python操作mysql

python操作mysql

pip install pymysql   

要操作数据库，首先就要建立和数据库的连接，配置pymysql.connect连接数据库：

con = pymysql.connect(
    host = '主机',
    port = 端口,
    user = '用户名',
    password = '密码',
    db = '数据库名',
    charset = 'utf8'
)

# 定义游标
cursor = con.cursor()  
cursor.execute('show databases')        #执行sql
one = cursor.fetchone()              #取出一条数据
all = cursor.fetchall()                 #取出所有数据
print(one)
print(all)

# 插入 
row = cursor.execute("insert into test(name,sex) values(%s,%s)", ('佳能','男'))

#  更新
row = cursor.execute("update test set name= '张三' where id = %s", (2,))

#  删除
cursor.execute('delete from user where id=%s ',(13,) )


# 关闭连接
con.commit()    #提交事物
cursor.close()  #关闭游标
con.close()      # 关闭连接


## 联合查询

union = '''
select  s.name, c.name,d.name  from  `student` s 
left join `select` se on se.s_id = s.s_id
left join course  c on se.c_id = c.id
left join department d on s.dept_id = d.id
ORDER BY s.name;
'''
# cursor.execute(union)
# find =cursor.fetchall()