import mysql.connector
class mydb():
def __init__(self):
self.connection = mysql.connector.connect(
host="localhost", # 数据库主机地址
user="root", # 数据库用户名
passwd="root", # 数据库密码
database="runoob_db",
)
self.cursor = self.connection.cursor()
self.db = 'runoob_db'
def createDB(self):
#判断数据库是否存在#
sql = "SHOW DATABASES"
self.cursor.execute(sql)
if self.cursor:
for db in self.cursor:
if db[0] == self.db:
hasDB=True
break
else:
hasDB=False
if hasDB == False:
sql = "CREATE DATABASE runoob_db"
self.cursor.execute(sql)
def showDB(self):
sql = "SHOW DATABASES"
self.cursor.execute(sql)
return self.cursor
def createTable(self):
sql="CREATE TABLE sites (name varchar(100),url varchar(200))"
self.cursor.execute(sql)
def innsertData(self):
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("RUNOOB", "https://www.runoob.com")
self.cursor.execute(sql, val)
self.connection.commit()
return self.cursor.rowcount
def innsertDates(self):
sql ="INSERT INTO sites (name, url) VALUE (%s, %s)"
val = [
('Google', 'https://www.google.com'),
('Github', 'https://www.github.com'),
('Taobao', 'https://www.taobao.com'),
('stackoverflow', 'https://www.stackoverflow.com/')
]
self.cursor.executemany(sql, val)
self.connection.commit()
return self.cursor.rowcount
def queryData(self, columns = "*", where="1=1"):
sql = "SELECT " + columns +" FROM sites" + " WHERE " + where
self.cursor.execute(sql)
data = self.cursor.fetchall()
return data
def deleteData(self, whereItems):
sql = "DELETE FROM sites WHERE "
if whereItems:
for key, value in whereItems.items():
sql += key + "='" + value + "'"
self.cursor.execute(sql)
self.connection.commit()
return self.cursor.rowcount
db = mydb()
"""打印所有数据库"""
"""
databases = db.showDB()
if databases:
for x in databases:
print(x)
"""
"""添加单条数据"""
#db.innsertData()
"""批量添加数据"""
#db.innsertDates();
"""批量查询数据"""
nums = db.queryData('url')
if nums:
for x in nums:
print(x)
""""条件查询"""
#nums = db.queryData('url', 'name="Google"')
#print(nums)
"""条件删除"""
#where = {'name': 'Google'}
#db.deleteData(where)
