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()