pip installl pymysql
def selectData():
dbconn = pymysql.connect(host="192.168.183.10",user="admin",password="redhat",database="it")
cr = dbconn.cursor()
test_sql = "select ip,name,password,port from servers"
result = cr.execute(test_sql)
print("结果行数:",result)
#获取实际的数据
data_01 = cr.fetchall()
print(data_01)
for ip,name,password,port in data_01:
print("ssh %s@% -p %s" % (name,ip,port))
# fetchmany(2),获取结果的前2行
data_02 = cr.fetchmany(1)
print(data_02)
#fetchone(),按行获取结果
data_03 = cr.fetchone()
print(data_03)
data_04 = cr.fetchone()
print(data_04)
无的话为none
cr.close()
dbconn.close()
if __name__ == '__main__':
selectData()
def writeData():
dbconn = pymysql.connect(host="192.168.183.10",user="admin",password="redhat",database="it")
cr = dbconn.cursor()
insert_sql = "insert into servers(ip,name,password,port) values('10.1.1.1','admin','123','555')"
insert_sql = "update servers set password='www.1.com' where ip='10.1.1.1'"
delete_sql = "delete from servers where ip='10.1.1.1'"
result = cr.execute(insert_sql)
print("行数:",result)
# 提交事务,提交写操作
dbconn.commit()
result = cr.execute(update_sql)
print("行数:",result)
# 提交事务,提交写操作
dbconn.commit()
result = cr.execute(delete_sql)
print("行数:",result)
# 提交事务,提交写操作
dbconn.commit()
cr.close()
dbconn.close()
if __name__ == '__main__':
writeData()
pip install pandas openpyxl
import pandas
pandas.read_excel(excel_file)
import pymysql
import sys
# 向yw表中添加数据
def add_data_to_table_yw(name,email,phone):
try:
dbconn = pymysql.connect(host="192.168.183.10",user="admin",password="123",database="it")
execpt Exception as e:
print("数据库连接创建失败!!!")
print(e)
sys.exit()
cr = dbconn.cursor()
add_data_sql = "insert into yw(name,email,phone) values('%s','%s','%s')" % (name,email,phone)
cr.execute(add_data_sql)
dbconn.commit()
cr.close()
dbconn.close()
# 从text.xlsx文件中读取数据,写入数据库
def read_test_to_db():
excel_file = r"D:\project\test.xlsx"
data = pandas.read_excel(excel_file)
print(data)
#pandas读取的数据类型默认为DataFrame,itertuplues()将数据转换为元组
for index,id,name,phone,email in data.itertuples():
add_data_to_table_yw(name=name,email=email,phone=phone)
if __name__ == '__main__':
read_test_to_db()