python-mysql
创建数据库连接
import mysql.connector
mydb = mysql.connector.connect(
host='localhost',
db='scrapymysql',
user='root',
password='******',
)
创建数据库
mycursor = mydb.cursor()
mycursor.execute('CREATE DATABASE database_name)
创建数据表
mycursor = mydb.cursor()
mycursor.execute('CREATE TABLE table_name(name VARCHAR(255),url VARCHAR(255),)')
注:实际使用中,我们只需调用import pymysql
pymysql.connect()
显示数据库列表:
mycursor.execute('SHOW DATABASE')
for i in mycursor:
print(i)
插入单数据:
sql = 'INSERT INTO table_name (name,url)VALUES (%s,%s)'
val = ('','')
mycursor.execute(sql,val)
mydb.commit()
插入多组数据
mycursor = mydb.cursor()
sql = 'INSERT INTO table_name (name,url) VALUES (%s,%s)'
val = [
('',''),
('','')
]
mycursor.executemany(sql,val)
mydb.commit()
添加主键id
mycursor.execute('ALTER TABLE website ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY')
查找数据
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM website")
myresult = mycursor.fetchall()#fetchall()获取所有记录
注:容易忽略execute中的“”
fetchone()
读取一条数据
选取特定数据:
mycursor.execute(SELECT name,url FROM website)
where语句:
mycursor.execute(SELECT * FROM website where name=’’)
指定数据
排序
mycursor.execute(“SELECT * FROM website ORDER BY name ASCS/DESC”)