Python复习之MySQL操作

Python复习之MySQL操作

MySQL的安装

pip/(conda) install mysql-connector-python

MySQL的函数

from mysql import connector
frommysql.connector import Error
try{
connection = connector.connect(host = host_name, user= user_name, passwd = user_password
database = da_name
)

}except Error as e:
print(e)
cursor = connection.cursor()
cursor.execute(sql——sentence)#将sql语句放入对象中
result = cursor.fetchall() # 获取所有的返回结果
myresult = mycursor.fetchone() #只获取一条结果
connection.commit()#数据表内容有更新时，必须使用该函数

创建表site

connection = connector.connect(
host=“localhost”,
user=“root”,
passwd=“123456”,
database=“runoob_db”
)
cursor = connection.cursor()
cursor.execute(“CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))”)

设置表的主键

cursor.execute(“ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY”)

数据插入

sql = “INSERT INTO sites (name, url) VALUES (%s, %s)”
val = (“RUNOOB”, “https://www.runoob.com”)
cursor.execute(sql, val)
connection.commit()#数据表内容有更新时，必须使用该函数
print(“1 条记录已插入, ID:”, mycursor.lastrowid)
--------------------------------批量数据插入--------------------------
sql = “INSERT INTO sites (name, url) VALUES (%s, %s)”
val = [
(‘Google’, ‘https://www.google.com’),
(‘Github’, ‘https://www.github.com’),
(‘Taobao’, ‘https://www.taobao.com’),
(‘stackoverflow’, ‘https://www.stackoverflow.com/’)
]
cursor.executemany(sql, val)
connection.commit() # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, " 条记录被插入")