Install
python3 -m pip install mysql-connector-python
Examples
Basic Usage
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword"
)
mycursor = mydb.cursor()
#####
mycursor.execute("CREATE DATABASE mydatabase")
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor.execute("CREATE TABLE pets (name VARCHAR(255), species VARCHAR(255))")
mycursor.execute("CREATE TABLE pets (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), species VARCHAR(255))")
mycursor.execute("ALTER TABLE pets ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
#####
sql = "INSERT INTO pets (name, species) VALUES (%s, %s)"
val = ("Tiddles", "cat")
mycursor.execute(sql, val)
mydb.commit()
sql = "INSERT INTO pets (name, species) VALUES (%s, %s)"
val = [
('Tiddles', 'cat'),
('Mr Flibble', 'penguin puppet'),
('Rover', 'dog'),
('Panda', 'hamster'),
('Bill', 'guinea pig'),
('Ben', 'guinea pig'),
('Podgie', 'cat')
]
mycursor.executemany(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)
#####
mycursor.execute("SELECT * FROM pets")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
mycursor.execute("SELECT name, species FROM pets")
myresult = mycursor.fetchall()
mycursor.execute("SELECT * FROM pets")
myresult = mycursor.fetchone()
sql = "SELECT * FROM pets WHERE species = 'guinea pig'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
sql = "SELECT * FROM pets WHERE name LIKE '%bob%'"
sql = "SELECT * FROM pets WHERE name = %s"
adr = ("Podgie", )
mycursor.execute(sql, adr)
