<jupyter_start><jupyter_text>connect mysql- db<jupyter_code>db = MySQLdb.connect(
    "IP",
    "root",
    "password",
    "db name",
    charset='utf8')
db<jupyter_output><empty_output><jupyter_text>create cursor<jupyter_code>cur = db.cursor()<jupyter_output><empty_output><jupyter_text>get columns name<jupyter_code>query = """
SHOW columns FROM country
"""
cur.execute(query)
columns = cur.fetchall()
for col in columns:
    print(col[0])<jupyter_output>Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2<jupyter_text>select<jupyter_code># query
query = """
SELECT code, name, population
FROM country
"""
cur.execute(query)<jupyter_output><empty_output><jupyter_text>create - database- table<jupyter_code>query = """
create database city_info_3;
"""
cur.execute(query)
query = """
use city_info_3;
"""
cur.execute(query)
query = """
CREATE TABLE popular(
    rank int(4),
    city_name varchar(20),
    population int(20)
)
"""
cur.execute(query)<jupyter_output><empty_output><jupyter_text>insert<jupyter_code>query ="""
INSERT INTO popular (rank, city_name, population)
VALUES  (1, "Seoul", 9900000),
        (2, "Busan", 3500000),
        (3, "Incheon", 2900000);
"""
cur.execute(query)<jupyter_output><empty_output><jupyter_text>동기화- cur.commit()<jupyter_code>db.commit()<jupyter_output><empty_output><jupyter_text>---  display and db -> pd- cur.fetchall()- df = pd.read_sql(query, db)  1)<jupyter_code>rows = cur.fetchall()
for row in rows[:5]:
    print(row)
df = pd.DataFrame(list(rows), columns=["code", "city_name", "population"])
df[:5]<jupyter_output><empty_output><jupyter_text>2)<jupyter_code>query = """
SELECT *
FROM popular
"""
cur.execute(query)
df = pd.read_sql(query, db)
df<jupyter_output><empty_output><jupyter_text>---  pd -> db- sqlAlchemy version == 1.2.5<jupyter_code>from sqlalchemy import create_engine
tips_df = sns.load_dataset("tips")
tips_df.tail()
engine = create_engine("mysql+mysqldb://root:password@192.168.127.12/city_info_3")
engine
tips_df.to_sql(name='tips', con=engine, if_exists="replace")
db.commit()
query = """
SELECT *
FROM tips;
"""
df = pd.read_sql(query, db)
df.tail()<jupyter_output><empty_output><jupyter_text>drop<jupyter_code>query = """
drop database city_info_3;
"""
cur = db.cursor()
cur.execute(query)<jupyter_output><empty_output><jupyter_text>connection close<jupyter_code>db.close()
db<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15671944782785222, 'fraction_numerical': 0.02192448233861145, 'mean_word_length': 7.078688524590164, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 50, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '157510', 'license': 'MIT', 'n_tokens_mistral': 991, 'n_tokens_neox': 948, 'n_words': 152, 'path': '05.DB&WEB/pr_mysql_with_py and sqlAlchemy.ipynb', 'repo_name': 'HenryPaik1/Study', 'size': '2461'}