【MySQL】PyCharm操作MySQL（增删改查）

连接数据库

• 两种“本地连接”方式：

  1. host=‘127.0.0.1’
  2. host=‘localhost’

  

• “远程连接”方式：
  网卡IP地址： host=‘10.36.151.86’
  

    import pymysql
  
    # 连接一个数据库
    # 下面2种连本机方式
    # db = pymysql.connect(host='127.0.0.1', user='root', password='123456',database='test1db')
    db = pymysql.connect(host='localhost', user='root', password='123456',database='test1db')
    # 远程方式
    # db = pymysql.Connect(host='10.36.151.86', user='root', password='123456',database='test1db')
  
    #创建一个游标
    cursor = db.cursor()
  
    #sql语句
    # sql = 'select version()'
    sql = 'select user()'
  
    # 执行sql语句
    cursor.execute(sql)
  
    # 取一行结果
    result = cursor.fetchone()
  
    # 打印
    print(result)
  

创建一个person表

import pymysql

# 创建一个person表


db = pymysql.Connect(host='10.36.151.86', user='root', password='123456',database='test1db')

#创建一个游标
cursor = db.cursor()

#sql语句
sql = '''
create table person(
id int not null primary key auto_increment,
name char(20) unique,
age int,
info varchar(100)
)
'''

# 执行sql语句
cursor.execute(sql)

# 取一行结果
result = cursor.fetchone()

# 打印
print(result)

插入一条数据

import pymysql

#插入一条数据
db = pymysql.Connect(host='10.36.151.86', user='root', password='123456',database='test1db')

#创建一个游标
cursor = db.cursor()

#sql语句
sql = "insert into person(name,age,info) values('二哈', 18, '我家有只小二哈')"


try:
	# 执行sql语句
	cursor.execute(sql)

	# 取一行结果
 	result = cursor.fetchone()

	# 打印
	print(result)
	# 提交事务
	db.commit()

except pymysql.Error as e:
	print(e)

	# 若出现错误，则回滚
	db.rollback()

db.close()

注意：要是重复运行会报错，因为名字设置唯一

查询

import pymysql

db = pymysql.Connect(host='10.36.151.86', user='root', password='123456',database='test1db')

#创建一个游标
cursor = db.cursor()

#sql语句
sql = "select * from person"

try:
	# 执行sql语句
	cursor.execute(sql)

	# 取全部（元组）
	result = cursor.fetchall()

	# 打印
	for person in result:
    	#print(result)
   		id1 = person[0]
    	name = person[1]
    	age = person[2]
    	info = person[3]

    	print(f"id={id1}, name={name}, age={age}, info={info}")

	# 提交事务
	db.commit()

except pymysql.Error as e:
	print(e)

	# 若出现错误，则回滚
	db.rollback()

db.close()

删

import pymysql

db = pymysql.Connect(host='10.36.151.86', user='root', password='123456',database='test1db')

#创建一个游标
cursor = db.cursor()

#sql语句-删除
sql = "delete from person where id=1"

try:
	# 执行sql语句
	count =cursor.execute(sql)

	# 打印受影响行数
	print(count)

	# 提交事务
	db.commit()

except pymysql.Error as e:
	print(e)

	# 若出现错误，则回滚
	db.rollback()

db.close()