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() # 关闭连接