pip install pymysql
# 连接数据库 DBHOST = 'localhost' DBUSER = 'root' DBPASS = '123456' DBNAME = 'bearpi' try: db = pymysql.connect(host=DBHOST,user=DBUSER,password=DBPASS,database=DBNAME) print("数据库连接成功!") except pymysql.Error as e: print("数据库连接失败"+str(e))
# 声明一个游标 cur = db.cursor() # 创建表前先检查表是否存在,如果存在则删除 cur.execute('DROP TABLE IF EXISTS My_information') # 编辑sql语句 sqlQuery = "CREATE TABLE My_information(Name CHAR(20) NOT NULL,Grade CHAR(20),Major CHAR(20),School_id bigint)" cur.execute(sqlQuery)
# 向表中插入数据 sqlQuery=" INSERT INTO My_information (Name, Grade, Major,School_id) VALUE (%s,%s,%s,%s) " #编辑sql语句 value=('张三','20级','计算机与通信工程',54201102021136) #编辑准备插入的值 # 执行sql语句 try: cur.execute(sqlQuery,value) db.commit() print('数据插入成功!') except pymysql.Error as e: print('数据插入失败:'+str(e) ) db.rollback()
# 查询表中的数据 sqlQuery = "SELECT * FROM My_information" # 使用fetchall()方法接收全部的返回结果 try: cur.execute(sqlQuery) results=cur.fetchall() for row in results: Name=row[0] Grade=row[1] Major=row[2] School_id=row[3] print('Name:%s;Grade:%s;Major:%s;School_id:%s'%(Name,Grade,Major,School_id)) except pymysql.Error as e: print("数据查询失败:" + str(e))