python基础-16-python连接mysql数据库

python连接mysql

1.修改mysql配置文件
默认mysql不支持远程连接

doublechina@doublechina:/etc/redis$ cd /etc/mysql
doublechina@doublechina:/etc/mysql$ cd mysql.conf.d/
doublechina@doublechina:/etc/mysql/mysql.conf.d$ ls
mysqld.cnf  mysqld_safe_syslog.cnf
doublechina@doublechina:/etc/mysql/mysql.conf.d$ sduo vim  mysqld.cnf
#搜索bind-address 
默认是127
修改为
bind-address            = 0.0.0.0


重启服务：
doublechina@doublechina:/etc/redis$ sudo service mysql restart

2.修改redis配置文件

doublechina@doublechina:/etc/mysql/mysql.conf.d$ cd /etc/redis
doublechina@doublechina:/etc/redis$ sudo vim  redis.conf
修改：
bind 127.0.0.1
修改后：
bind 0.0.0.0
搜索：#requirepass foobared

修改：#requirepass foobared
修改后：requirepass 你自己设置的密码

重启服务：
doublechina@doublechina:/etc/redis$ sudo service redis-server restart

3.配置虚拟机端口转发

#mysql 
主端口：33333   
mysql默认 3306

主端口：55555
redis默认：6379

4.原生连接mysql，使用不多

安装mysql和redis支持

pip3 install  pymysql
pip3 install redis

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

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

import pymysql

sqlagr = {
    "host": "xxx",
    "port": 3306,
    "user": "xxx",
    "password": "xxx",
    "db": "mydb",
    "charset": 'utf8'
}
conn = pymysql.connect(**sqlagr)
cursor = conn.cursor()
# 返回影响列
row = cursor.execute('show databases')
one=cursor.fetchone()
all=cursor.fetchall()
many=cursor.fetchmany(2)
print(all)
print(one)
print(many)
print(row)
print("--------------------")
for i  in  all:
    print(*i)

#python创建一个表
table='''
create table user(
  id INT  PRIMARY  KEY  auto_increment,
  username VARCHAR (20) not  NULL ,
   password VARCHAR (20) not  NULL
 )
 '''
 cursor.execute(table)

###插入值
sql='insert into  user(username,password) VALUES (%s,%s)'
cursor.execute(sql,("python","123456"))
cursor.execute(sql,("golang","123456"))
#插入多条
cursor.executemany(sql,[("kotlin","123456"),("c++","123456")])

###更新
updateSql="update user set username=%s where id=%s "
cursor.execute(updateSql,("c","6"))


###删除操作
delSql="delete from user  where id=%s "
cursor.execute(delSql,("6",))



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