<jupyter_start><jupyter_text>MySQL 8.0 Reference Manual  Basic Steps for MySQL Server Deployment with Docker  Starting a MySQL Server Instance MySQL docker official image<jupyter_code>!docker run --name=mysql1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<PASSWORD> -d mysql<jupyter_output><empty_output><jupyter_text>MySQL docker Oracle image```docker run --name=mysql1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<PASSWORD> -d mysql/mysql-server```  Connecting to MySQL Server from within the Container ```docker exec -it mysql1 mysql -uroot -pmysqlpass```  Container Shell Access ```docker exec -it mysql1 bash ```  Stopping and Deleting a MySQL Container ```docker stop mysql1``` ```docker restart mysql1``` ```docker rm mysql1```  Tutorial  Connecting to and Disconnecting from the Server ```mysql -u root -p```<jupyter_code>from sqlalchemy import create_engine, inspect, Table, Column, String, Integer, ForeignKey, MetaData, CHAR, VARCHAR, DATE, select, desc, func, text
from sqlalchemy.sql import and_, or_, not_, alias
from sqlalchemy.dialects.mysql import INTEGER, DECIMAL
import pandas as pd
engine = create_engine('mysql+pymysql://root:mysqlpass@localhost', pool_recycle=3600)
conn = engine.connect()
def query(s):
    result = conn.execute(s)
    for row in result:
        print(row)<jupyter_output><empty_output><jupyter_text>Creating and Selecting a Database ```sqlSHOW DATABASES;```<jupyter_code>def get_dbs():
    insp = inspect(engine)
    dbs = insp.get_schema_names()
    print(dbs)
    
get_dbs()<jupyter_output><empty_output><jupyter_text>```sqlCREATE DATABASE menagerie;```<jupyter_code>def set_queries(queries):
    for query in queries:
        result = conn.execute(query)
      
queries = [
    'DROP DATABASE IF EXISTS menagerie;',
    'CREATE DATABASE menagerie;'
]

set_queries(queries)

get_dbs()
conn.close()
engine = create_engine('mysql+pymysql://root:mysqlpass@localhost/menagerie', pool_recycle=3600)
conn = engine.connect()<jupyter_output><empty_output><jupyter_text>Creating a Table ```sqlSHOW TABLES;```<jupyter_code>def show_tables():
    print(engine.table_names())
    
show_tables()<jupyter_output><empty_output><jupyter_text>```sqlCREATE TABLE pet (    name VARCHAR(20),     owner VARCHAR(20),    species VARCHAR(20),     sex CHAR(1),     birth DATE,     death DATE);```<jupyter_code>meta = MetaData()

pet = Table('pet', meta,
            Column('name', VARCHAR(20)),
            Column('owner', VARCHAR(20)),
            Column('species', VARCHAR(20)),
            Column('sex', CHAR(1)),
            Column('birth', DATE),
            Column('death', DATE)
           )
       
pet.create(engine)
show_tables()<jupyter_output><empty_output><jupyter_text>```sqlDESCRIBE pet;```<jupyter_code>print(meta.tables)<jupyter_output><empty_output><jupyter_text>Loading Data into a Table ```sqlINSERT INTO pet VALUES     ('Fluffy',   'Harold',   'cat',      'f',    '1993-02-04',   NULL),    ('Claws',    'Diane',    'cat',      'm',    '1994-03-17',   NULL),    ('Buffy',    'Harold',   'dog',      'f',    '1989-05-13',   NULL),    ('Fang',     'Benny',    'dog',      'm',    '1990-08-27',   NULL),    ('Bowser',   'Diane',    'dog',      'm',    '1979-08-31',   '1995-07-29'),    ('Chirpy',   'Gwen',     'bird',     'f',    '1998-09-11',   NULL),    ('Whistler', 'Gwen',     'bird',     NULL,   '1997-12-09',   NULL),    ('Slim',     'Benny',    'snake',    'm',    '1996-04-29',   NULL),    ('Puffball', 'Diane',    'hamster',  'f',    '1999-03-30',   NULL);```<jupyter_code>cols = [str(col).split('.')[1] for col in pet.columns]

ins_data = [
    ['Fluffy',	    'Harold',   'cat',	    'f',	'1993-02-04',	None],
    ['Claws',	    'Gwen',     'cat',	    'm',	'1994-03-17',	None],
    ['Buffy',	    'Harold',	'dog',	    'f',	'1989-05-13',	None],
    ['Fang',	    'Benny',	'dog',	    'm',	'1990-08-27',	None],
    ['Bowser',	    'Diane',	'dog',	    'm',	'1979-08-31',	'1995-07-29'],
    ['Chirpy',	    'Gwen',     'bird',	    'f',	'1998-09-11',	None],
    ['Whistler',    'Gwen',     'bird',	    None,	'1997-12-09',	None],
    ['Slim',	    'Benny',	'snake',	'm',	'1996-04-29',	None],
    ['Puffball',    'Diane',	'hamster',	'f',	'1999-03-30',	None]
]

data = [{col: d for col, d in zip(cols, ds)} for ds in ins_data]
result = conn.execute(pet.insert(), data)<jupyter_output><empty_output><jupyter_text>Selecting All Data ```sqlSELECT * FROM pet;```<jupyter_code>s = select([pet])
query(s)<jupyter_output><empty_output><jupyter_text>```sqlUPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';```<jupyter_code>stmt = pet.update().\
            where(pet.c.name == 'Bowser').\
            values(birth = '1989-08-31')
result = conn.execute(stmt)<jupyter_output><empty_output><jupyter_text>Selecting Particular Rows ```sqlSELECT * FROM pet WHERE name = 'Bowser';```<jupyter_code>s = select([pet]).where(pet.c.name == 'Bowser')
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT * FROM pet WHERE birth >= '1998-1-1';```<jupyter_code>s = select([pet]).where(pet.c.birth >= '1998-1-1')
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT * FROM pet WHERE species = 'dog' AND sex = 'f';```<jupyter_code>s = select([pet]).\
    where(
        and_(pet.c.species == 'dog', pet.c.sex == 'f')
        )
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT * FROM pet WHERE species = 'snake' OR species = 'bird';```<jupyter_code>s = select([pet]).\
    where(
        or_(pet.c.species == 'snake', pet.c.species == 'bird')
        )
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');```<jupyter_code>s = select([pet]).\
    where(
        or_(
            and_(pet.c.species == 'cat', pet.c.sex == 'm'
                ),
            and_(pet.c.species == 'dog', pet.c.sex == 'f'
                )
            )
        )
query(s)<jupyter_output><empty_output><jupyter_text>Selecting Particular Columns ```sqlSELECT name, birth FROM pet;```<jupyter_code>s = select([pet.c.name, pet.c.birth])
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT owner FROM pet;```<jupyter_code>s = select([pet.c.owner])
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT DISTINCT owner FROM pet;```<jupyter_code>s = select([pet.c.owner]).distinct()
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';```<jupyter_code>s = select([pet.c.name, pet.c.species, pet.c.birth]).\
        where(
            or_(pet.c.species == 'dog', pet.c.species == 'cat')
            )  
query(s)<jupyter_output><empty_output><jupyter_text>Sorting Rows ```sqlSELECT name, birth FROM pet ORDER BY birth;```<jupyter_code>s = select([pet.c.name, pet.c.birth]).order_by('birth')     
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT name, birth FROM pet ORDER BY birth DESC;```<jupyter_code>s = select([pet.c.name, pet.c.birth]).order_by(desc('birth'))     
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT name, species, birth FROM pet ORDER BY species, birth DESC;```<jupyter_code>s = select([pet.c.name, pet.c.species, pet.c.birth]).order_by('species', desc('birth'))     
query(s)<jupyter_output><empty_output><jupyter_text>Date Calculations ```sqlSELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;```<jupyter_code>s = select([pet.c.name, pet.c.birth, func.curdate(), func.timestampdiff(text('YEAR'), pet.c.birth, func.curdate())])   
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM petORDER BY name;```<jupyter_code>s = select([pet.c.name, pet.c.birth, func.curdate(), func.timestampdiff(text('YEAR'), pet.c.birth, func.curdate()).label('age')]).order_by('name')   
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM petORDER BY age;```<jupyter_code>s = select([pet.c.name, pet.c.birth, func.curdate(), func.timestampdiff(text('YEAR'), pet.c.birth, func.curdate()).label('age')]).order_by('age')
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM petWHERE death IS NOT NULLORDER BY age;```<jupyter_code>s = select([pet.c.name, pet.c.birth, pet.c.death, func.timestampdiff(text('YEAR'), pet.c.birth, pet.c.death).label('age')]).\
    where(pet.c.death != None).order_by('age')
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT name, birth, MONTH(birth) FROM pet;```<jupyter_code>s = select([pet.c.name, pet.c.birth, func.month(pet.c.birth)])
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT name, birth FROM pet WHERE MONTH(birth) = 5;```<jupyter_code>s = select([pet.c.name, pet.c.birth]).\
        where(func.month(pet.c.birth) == 5)
query(s)<jupyter_output><empty_output><jupyter_text>Pattern Matching To find names beginning with b:```sqlSELECT * FROM pet WHERE name LIKE 'b%';```<jupyter_code>s = select([pet]).\
        where(pet.c.name.like('b%'))
query(s)<jupyter_output><empty_output><jupyter_text>To find names ending with fy:```sqlSELECT * FROM pet WHERE name LIKE '%fy';```<jupyter_code>s = select([pet]).\
        where(pet.c.name.like('%fy'))
query(s)<jupyter_output><empty_output><jupyter_text>To find names containing a w```sqlSELECT * FROM pet WHERE name LIKE '%w%';```<jupyter_code>s = select([pet]).\
        where(pet.c.name.like('%w%'))
query(s)<jupyter_output><empty_output><jupyter_text>To find names containing exactly five characters, use five instances of the _ pattern character:```sqlSELECT * FROM pet WHERE name LIKE '%w%';```<jupyter_code>s = select([pet]).\
        where(pet.c.name.like('_____'))
query(s)<jupyter_output><empty_output><jupyter_text>To find names beginning with b, use ^ to match the beginning of the name:```sqlSELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');```<jupyter_code>s = select([pet]).\
        where(func.regexp_like(pet.c.name, '^b'))
query(s)<jupyter_output><empty_output><jupyter_text>To find names ending with `fy`, use `$` to match the end of the name:```sqlSELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy%');```<jupyter_code>s = select([pet]).\
        where(func.regexp_like(pet.c.name, 'fy$'))
query(s)<jupyter_output><empty_output><jupyter_text>To find names containing a `w`, use this query:```sqlSELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');```<jupyter_code>s = select([pet]).\
        where(func.regexp_like(pet.c.name, 'w'))
query(s)<jupyter_output><empty_output><jupyter_text>To find names containing exactly five characters, use `^` and `$` to match the beginning and end of the name, and five instances of . in between:```sqlSELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');```<jupyter_code>s = select([pet]).\
        where(func.regexp_like(pet.c.name, '^.....$'))
query(s)<jupyter_output><empty_output><jupyter_text>You could also write the previous query using the {**n**} (“*repeat-**n**-times*”) operator:```sqlSELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');```<jupyter_code>s = select([pet]).\
        where(func.regexp_like(pet.c.name, '^.{5}$'))
query(s)<jupyter_output><empty_output><jupyter_text>Counting Rows ```sqlSELECT COUNT(*) FROM pet;```<jupyter_code>s = select([func.count()]).select_from(pet)
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT owner, COUNT(*) FROM pet GROUP BY owner;```<jupyter_code>s = select([pet.c.owner, func.count()]).group_by('owner')
query(s)<jupyter_output><empty_output><jupyter_text>Number of animals per species:```sqlSELECT species, COUNT(*) FROM pet GROUP BY species;```<jupyter_code>s = select([pet.c.species, func.count()]).group_by('species')
query(s)<jupyter_output><empty_output><jupyter_text>Number of animals per sex:```sqlSELECT sex, COUNT(*) FROM pet GROUP BY sex;```<jupyter_code>s = select([pet.c.sex, func.count()]).group_by('sex')
query(s)<jupyter_output><empty_output><jupyter_text>Number of animals per combination of species and sex:```sqlSELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;```<jupyter_code>s = select([pet.c.species, pet.c.sex, func.count()]).group_by('species', 'sex')
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT species, sex, COUNT(*) FROM petWHERE species = 'dog' OR species = 'cat'GROUP BY species, sex;```<jupyter_code>s = select([pet.c.species, pet.c.sex, func.count()]).\
        where(or_(pet.c.species == 'dog', pet.c.species == 'cat')).\
        group_by('species', 'sex')
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT species, sex, COUNT(*) FROM petWHERE sex IS NOT NULLGROUP BY species, sex;```<jupyter_code>s = select([pet.c.species, pet.c.sex, func.count()]).\
        where(pet.c.sex != None).\
        group_by('species', 'sex')
query(s)<jupyter_output><empty_output><jupyter_text>Using More Than one Table ```sqlCREATE TABLE event (    name VARCHAR(20),     date DATE,    type VARCHAR(15),     remark VARCHAR(255));```<jupyter_code>meta = MetaData()

event = Table('event', meta,
            Column('name', VARCHAR(20)),
            Column('date', DATE),
            Column('type', VARCHAR(15)),
            Column('remark', VARCHAR(255))
           )
       
event.create(engine)
show_tables()<jupyter_output><empty_output><jupyter_text>```sqlINSERT INTO event VALUES     ('Fluffy',	    '1995-05-15',   'litter',	'4 kittens, 3 female, 1 male'),    ('Buffy',	    '1993-06-23',	'litter',	'5 puppies, 2 female, 3 male'),    ('Buffy',	    '1994-06-19',	'litter',	'3 puppies, 3 female'),    ('Chirpy',	    '1999-03-21',	'vet',      'needed beak straightened'),    ('Slim',	    '1997-08-03',	'vet',	    'broken rib'),    ('Bowser',	    '1991-10-12',	'kennel',   NULL),    ('Fang',	    '1991-10-12',	'kennel',   NULL),    ('Fang',	    '1998-08-28',	'birthday',	'Gave him a new chew toy'),    ('Claws',	    '1998-03-17',	'birthday',	'Gave him a new flea collar'),    ('Whistler',   '1998-12-09',	'birthday',	'First birthday');```<jupyter_code>cols = [str(col).split('.')[1] for col in event.columns]

ins_data = [
    ['Fluffy',	    '1995-05-15',   'litter',   '4 kittens, 3 female, 1 male'],
    ['Buffy',	    '1993-06-23',	'litter',   '5 puppies, 2 female, 3 male'],
    ['Buffy',	    '1994-06-19',	'litter',   '3 puppies, 3 female'],
    ['Chirpy',	    '1999-03-21',	'vet',      'needed beak straightened'],
    ['Slim',	    '1997-08-03',	'vet',	    'broken rib'],
    ['Bowser',	    '1991-10-12',	'kennel',   None],
    ['Fang',	    '1991-10-12',	'kennel',   None],
    ['Fang',	    '1998-08-28',	'birthday', 'Gave him a new chew toy'],
    ['Claws',	    '1998-03-17',	'birthday', 'Gave him a new flea collar'],
    ['Whistler',    '1998-12-09',	'birthday', 'First birthday']
]

data = [{col: d for col, d in zip(cols, ds)} for ds in ins_data]
result = conn.execute(event.insert(), data)
s = select([event])
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT pet.name, TIMESTAMPDIFF(YEAR,birth,date) AS age, remarkFROM pet INNER JOIN event ON pet.name = event.nameWHERE event.type = 'litter';```<jupyter_code>s = select([pet.c.name, func.timestampdiff(text('YEAR'), pet.c.birth, text('date')).label('age'), event.c.remark]).\
        select_from(pet.join(event, pet.c.name == event.c.name)).\
        where(event.c.type == 'litter')
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT p1.name, p1.sex, p2.name, p2.sex, p1.speciesFROM pet AS p1 INNER JOIN pet AS p2ON p1.species = p2.speciesAND p1.sex = 'f' AND p1.death IS NULLAND p2.sex = 'm' AND p2.death IS NULL;```<jupyter_code>p1 = pet.alias('p1')
p2 = pet.alias('p2')
s = select([p1.c.name, p1.c.sex, p2.c.name, p2.c.sex, p1.c.species]).\
        select_from(p1.join(p2, and_(
            p1.c.species == p2.c.species, and_(
                p1.c.sex == 'f', and_(
                    p1.c.death == None, and_(
                        p2.c.sex == 'm', p2.c.death == None))))))
query(s)<jupyter_output><empty_output><jupyter_text>Examples of Common Queries ```sqlCREATE TABLE shop (    article INT UNSIGNED  DEFAULT '0000' NOT NULL,    dealer  CHAR(20)      DEFAULT ''     NOT NULL,    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,    PRIMARY KEY(article, dealer));INSERT INTO shop VALUES    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);```<jupyter_code>meta = MetaData()

shop = Table('shop', meta,
            Column('article', INTEGER(unsigned=True), server_default='0000', primary_key=True),
            Column('dealer', CHAR(20), server_default='', primary_key=True),
            Column('price', DECIMAL(16,2), server_default='0.00', nullable=False)
           )
       
shop.create(engine)
show_tables()

raw_data = [
    [1, 'A', 3.45], [1, 'B', 3.99], [2, 'A', 10.99], [3, 'B', 1.45],
    [3, 'C', 1.69], [3, 'D', 1.25], [4, 'D', 19.95]
]

ins_data = [{col: data for col, data in zip(shop.c.keys(), row)} for row in raw_data]
result = conn.execute(shop.insert(), ins_data)
s = select([shop])
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT * FROM shop ORDER BY article;```<jupyter_code>s = select([shop]).order_by('article')
query(s)<jupyter_output><empty_output><jupyter_text>**The Maximum Value for a Column** ```sqlSELECT MAX(article) AS article FROM shop;```<jupyter_code>s = select([func.max(shop.c.article).label('article')])
query(s)<jupyter_output><empty_output><jupyter_text>**The Row Holding the Maximum of a Certain Column** ```sqlSELECT article, dealer, priceFROM   shopWHERE  price=(SELECT MAX(price) FROM shop);```<jupyter_code>s = select([shop.c.article, shop.c.dealer, shop.c.price]).\
        where(shop.c.price == (select([func.max(shop.c.price)])))
query(s)<jupyter_output><empty_output><jupyter_text>```sqlSELECT article, dealer, priceFROM shopORDER BY price DESCLIMIT 1;```<jupyter_code>s = select([shop.c.article, shop.c.dealer, shop.c.price]).\
        order_by(desc(shop.c.price)).limit(1)
query(s)<jupyter_output><empty_output><jupyter_text>**Maximum of Column per Group** ```sqlSELECT article, MAX(price) AS priceFROM   shopGROUP BY articleORDER BY article;```<jupyter_code>s = select([shop.c.article, func.max(shop.c.price).label('price')]).\
        group_by(shop.c.article).\
        order_by(shop.c.article)
query(s)
conn.close()
engine = create_engine('mysql+pymysql://root:mysqlpass@localhost', pool_recycle=3600)
# engine = create_engine('mysql+pymysql://root:mysqlpass@localhost/menagerie', pool_recycle=3600)
conn = engine.connect()
insert_list = [(43, 'F', 2.37),(22, 'O', 2.35)]

tablename = 'menagerie.shop'
schema, table_name = tablename.split('.')
meta = MetaData()
shop = Table(table_name, meta, autoload=True, autoload_with=engine, schema=schema)

insert_list = [{col: data for col, data in zip(shop.c.keys(), row)} for row in insert_list]
print(insert_list)
#result = conn.execute(shop.insert(), insert_list)
tablename = 'menagerie.kmeans_us_arrests_data'
schema, table_name = tablename.split('.')

df_train = pd.read_sql_table(table_name, engine, schema=schema)
print(the_frame)
print("\nHead(10) of Train data:")
print(df_train.head(10))
# A dictionary to get 'sno' to 'state' mapping. Required for plotting.
df1 = df_train.select(['sno', 'state'])
insp = inspect(engine)
dbs = insp.get_schema_names()
print(dbs)
insp.get_table_names(schema='menagerie')
insp.get_table_comment('kmeans_us_arrests_data', schema='menagerie')
insp.get_columns('kmeans_us_arrests_data', schema='menagerie')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.190234914785813, 'fraction_numerical': 0.029121244690106966, 'mean_word_length': 4.683536940081443, 'pattern_counts': {'":': 0, '<': 219, '<?xml version=': 0, '>': 221, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '54', 'dir': 'jupyter-structured-clean-dedup', 'id': '383009', 'license': 'MIT', 'n_tokens_mistral': 7850, 'n_tokens_neox': 7194, 'n_words': 1430, 'path': 'notebooks/mysql_tutorial.ipynb', 'repo_name': 'eavelardev/my-dev-repo', 'size': '19537'}