import pymysql
ip = "xxx.xxx.xx.xx"
user = "123"
password = "123456" #必须是字符串
db = "123"
port = 3306 #必须得写int类型
connect = pymysql.connect(host=ip,user=user,
password=password,db=db,port=port,
autocommit=True(操作数据库后不用手动提交),charset="utf8")
cursor = connect.cursor(pymysql.cursors.DictCursor(指定游标为字典类型))
sql = "show tables;"
sql = "create table fmz (id int primary key auto_increment,name varchar(50) not null,sex int default 0, phone varchar(11) unique );"
sql = "select * from fmz"
sql = 'insert into fmz (name,phone) values ("周杰伦1","32235231");'
cursor.execute(sql) #执行sql语句
如果插入数据有变量,按照以下格式:
add_user = "insert into user_ww(ID,name,pword) values('%s','%s','%s')" % (row, username, password)
print(cursor.description) #获取表里字段的描述
insert into fmz (name,phone) values ("周杰伦","3223523");
update fmz set sex=1 where id =1 ;
delete from fmz where id =3 ;
result = cursor.fetchall() #始终返回的都是一个二维数组,获取sql执行的所有结果
connect.rollback() #回滚
connect.commit() #提交
print(result)
print(cursor.fetchone())#只获取一条
print(cursor.fetchmany(5))#获取n条数据
for c in cursor: 直接循环游标,每次取的就是表里面的每一条数据
print(c)
cursor.close()
connect.close()
select * form xxx where id =1;
def op_mysql(mysql_info: dict, sql: str,all=True):
connect = pymysql.connect(**mysql_info)
host="xxxxxxx",user="123","password":"xx"
cursor = connect.cursor()
cursor.execute(sql)
result = None
if sql.strip().lower().startswith("select"):
if all:
result = cursor.fetchall()
else:
result = cursor.fetchone()
cursor.close()
connect.close()
return result
if __name__ == '__main__':
mysql_info = {"host":"xxx.xxx.xx.xx","user":"123",
"password":"123456","port":3306,"db":"jxz",
"autocommit":True,"charset":"utf8"}
result = op_mysql(mysql_info,"select * from fz;")
print(result)
result = op_mysql(mysql_info, "select * from fz where id =1;",False)
print(result)