PythonããMySQLã«æ¥ç¶ããŠããŒã¿ããŒã¹ãæäœããã«ã¯
PythonããMySQLããŒã¿ããŒã¹ãžã®æ¥ç¶ãšæäœãè¡ãæé ã解説ããŸããMySQLãšPythonã®é£æºãå ·äœçã«äœ¿ãããå Žé¢ãå¿ èŠãªã©ã€ãã©ãªã®å°å ¥ããå§ãŸããããŒã¿ããŒã¹ã®äœæãããŒãã«ã®æäœãããŒã¿ã®æ¿å ¥ã»ååŸã»æŽæ°ã»åé€ãŸã§ããµã³ãã«ã³ãŒããšãšãã«ç޹ä»ããŠããŸããã¯ããã«
Pythonã¯æ±çšæ§ãé«ããããŒã¿ããŒã¹ãšã®é£æºãåŸæãšãããŠããŸããMySQLã¯åºãå©çšãããããŒã¿ããŒã¹ç®¡çã·ã¹ãã ã§ãããPythonããMySQLãæäœããããšã§ãŠã§ãã¢ããªã±ãŒã·ã§ã³ã®éçºãããŒã¿è§£æããŠãŒã¶ãŒç®¡çãªã©å¹
åºãçšéã«æŽ»çšã§ããŸãã
æ¬èšäºã§ã¯PythonããMySQLããŒã¿ããŒã¹ã«æ¥ç¶ããŠåºæ¬çãªæäœãè¡ãæé ãããµã³ãã«ã³ãŒãã亀ããŠè§£èª¬ããŸãã
MySQLãšã¯ïŒ
MySQLã¯ãªãŒãã³ãœãŒã¹ãã€é«æ§èœãªãªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ç®¡çã·ã¹ãã (RDBMS)ã§ãããããŒã¿ã®æ ŒçŽãååŸãæŽæ°ãåé€ãªã©ã®æäœãè¡ãããšãã§ããŸããå€ãã®Webã¢ããªã±ãŒã·ã§ã³ããµãŒãã¹ã§å©çšãããä¿¡é Œæ§ãé«ãããšããéçºè ã«åºãæ¯æãããŠããŸãã
PythonããMySQLãæäœããã®ã¯ã©ããªå Žé¢ãïŒ
PythonããMySQLãæäœããå Žé¢ã¯å€å²ã«ããããŸãã以äžã¯ãã®äžäŸã§ãã
- ãŠã§ãã¢ããªã±ãŒã·ã§ã³éçº
- ãŠãŒã¶ãŒãååã®æ å ±ãã»ãã·ã§ã³ããŒã¿ãããŒã¿ããŒã¹ã«ä¿åããååŸããã
- ããŒã¿è§£æ
- 倧éã®ããŒã¿ãMySQLã«æ ŒçŽããPythonã䜿çšããŠããŒã¿ã®åæãå¯èŠåãè¡ãã
- 倧éã®ããŒã¿ãMySQLã«æ ŒçŽããPythonã䜿çšããŠããŒã¿ã®åæãå¯èŠåãè¡ãã
PythonããMySQLãæäœããããšã§æè»ãã€å¹ççãªããŒã¿ããŒã¹ç®¡çãå¯èœãšãªããããããŸããŸãªå Žé¢ã§æŽ»çšãããŠããŸãã
å¿ èŠãªã©ã€ãã©ãªïŒmysql-connector-pythonïŒãšãã®ã€ã³ã¹ããŒã«æ¹æ³
PythonããMySQLã«æ¥ç¶ããããã«ã¯ãmysql-connector-pythonãšããã©ã€ãã©ãªã䜿çšããŸãã以äžã«ãã®ã€ã³ã¹ããŒã«æ¹æ³ã瀺ããŸãã
pip install mysql-connector-python
ããã§Pythonã¹ã¯ãªããããMySQLã«æ¥ç¶ããéã«å¿ èŠãªã©ã€ãã©ãªãå°å ¥ãããŸãã
Pythonã§MySQLã®ããŒã¿ããŒã¹ãäœæãã
ãŸãã¯MySQLã«ããŒã¿ããŒã¹ãäœæããŠã¿ãŸãããã以äžããã®æé ã§ãã
MySQLèªäœã®ã€ã³ã¹ããŒã«ãç«ã¡äžãããŠãŒã¶ãŒäœæãšãã®ãã¹ã¯ãŒãèšå®ã¯å¥éè¡ãªã£ãŠãã ããã
æ¥ç¶å
ã®ãã¹ããå¥éææ¡ããŠãã ããã
ãã®èšäºã§ã¯localhostã«ãŠMySQLãåäœããŠããåæã§é²ããŠãããŸãã
import mysql.connector
# MySQLã«æ¥ç¶
conn = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password"
)
# ã«ãŒãœã«ãååŸ
cursor = conn.cursor()
# ããŒã¿ããŒã¹äœæ
cursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")
# æ¥ç¶ãéãã
cursor.close()
conn.close()
ããã§ã"mydatabase"ãšããååã®ããŒã¿ããŒã¹ãäœæãããŸãã
"your_username"ãš"your_password"ã¯ãèªèº«ã®MySQLã®ãŠãŒã¶ãŒåãšãã¹ã¯ãŒãã«çœ®ãæããŠãã ããããŸãã"mydatabase"ã¯äœæãããããŒã¿ããŒã¹ã®ååã«å€æŽããŠãã ããã
ã«ãŒãœã«ïŒcursorïŒãšã¯äœãïŒ
åé
ã§åºãŠããã«ãŒãœã«ïŒcursorïŒã«ã€ããŠå°ã解説ããŠãããŸãã
ã«ãŒãœã«ã¯ãããŒã¿ããŒã¹ãšã®å¯Ÿè©±ãã¯ãšãªã®å®è¡ãè¡ãããã®ä»®æ³ãã€ã³ã¿ã®ãããªãã®ã§ãã
PythonããMySQLããŒã¿ããŒã¹ã«æ¥ç¶ããéã«ããã®ã«ãŒãœã«ã䜿çšããŠããŒã¿ããŒã¹äžã®ã¯ãšãªãå®è¡ããŸãã
以äžã«cursorã®åœ¹å²ãšäž»ãªæäœã«ã€ããŠèª¬æããŸãã
ã«ãŒãœã«ã®ååŸ:
cursor = conn.cursor()
ããŒã¿ããŒã¹ãšã®æ¥ç¶ïŒconnïŒããã«ãŒãœã«ãååŸããŸãããã®ã«ãŒãœã«ãéããŠã¯ãšãªãå®è¡ããçµæãååŸã§ããŸãã
ã¯ãšãªã®å®è¡:
cursor.execute(sql_query)
executeã¡ãœããã䜿çšããŠãSQLã¯ãšãªãå®è¡ããŸããããã«ãããããŒã¿ããŒã¹äžã§æå®ãããæäœãå®è¡ãããŸãã
ããŒã¿ã®ååŸ:
result = cursor.fetchall()
fetchallã¡ãœããã䜿çšããŠãå®è¡ãããã¯ãšãªã®çµæãååŸããŸããååŸããããŒã¿ã¯Pythonã®ããŒã¿æ§é ã«å€æãããããã°ã©ã ã§å©çšã§ããŸãã
ããŒã¿ã®å€æŽã®ç¢ºå®:
conn.commit()
ããŒã¿ããŒã¹äžã§ã®å€æŽïŒæ¿å ¥ãæŽæ°ãåé€ãªã©ïŒã確å®ãããããã«ãcommitã¡ãœããã䜿çšããŸããããã«ããã倿Žãæ°žç¶çã«ããŒã¿ããŒã¹ã«åæ ãããŸãã
ã«ãŒãœã«ã®ã¯ããŒãº:
cursor.close()
ã«ãŒãœã«ãäžèŠã«ãªã£ãããcloseã¡ãœããã䜿çšããŠéããŸããããã«ãããããŒã¿ããŒã¹ãšã®æ¥ç¶ãè§£é€ãããŸãã
cursorã¯ããŒã¿ããŒã¹ãšã®ãããšããè¡ãããã®ã€ã³ã¿ãŒãã§ãŒã¹ã§ãããã¯ãšãªã®å®è¡ãçµæã®ååŸãããŒã¿ããŒã¹ãžã®å€æŽã®ç¢ºå®ãªã©ãè¡ããŸãã
ããŒã¿ããŒã¹ã«å¯Ÿããæäœã¯ããã®ã«ãŒãœã«ãä»ããŠè¡ãããããŒã¿ããŒã¹ãžã®æ¥ç¶ãç¶æããããŸãŸãè€æ°ã®ã¯ãšãªãæäœã鿬¡çã«å®è¡ã§ããŸãã
äœæããMySQLããŒã¿ããŒã¹ã«æ¥ç¶ãã
PythonããMySQLããŒã¿ããŒã¹ã«æ¥ç¶ããæé ã¯ä»¥äžã®éãã§ãããã®æ¥ç¶ã¯ããŒã¿ããŒã¹ãžã®æäœãè¡ãåã«è¡ãå¿ èŠããããŸãã
import mysql.connector
# MySQLã«æ¥ç¶
conn = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="mydatabase"
)
# ã«ãŒãœã«ãååŸ
cursor = conn.cursor()
# ããã§ããŒã¿ããŒã¹ãžã®æäœãè¡ã
# æ¥ç¶ãéãã
cursor.close()
conn.close()
ãã以éã¯ããŸããŸãªã±ãŒã¹ã§PythonããMySQLãžã¯ãšãªãæž¡ããŠå®è¡ãããµã³ãã«ã§ãã
ããããäžèšã®ãµã³ãã«ã³ãŒãå
ã«ããã#ããã§ããŒã¿ããŒã¹ãžã®æäœãè¡ãããšããç®æã«åœãŠã¯ããŠå®è¡ããŠã¿ãŠãã ããã
MySQLã®ããŒã¿ããŒã¹ã«ããŒãã«ãäœæãã
äœæããããŒã¿ããŒã¹ã«ããŒãã«ãäœæããã«ã¯ä»¥äžã®ã³ãŒããå®è¡ããŸãã
# ããŒãã«äœæã®ã¯ãšãª
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
)
"""
# ããŒãã«äœæ
cursor.execute(create_table_query)
ããã§ã"users"ãšããååã®ããŒãã«ãäœæãããŸãããããŒãã«ã¯idãusernameãemailã®3ã€ã®ã«ã©ã ã§æ§æãããŠããŸãã
ããŒãã«ã«ããŒã¿ãæ¿å ¥ãã
ããŒãã«ã«ããŒã¿ãæ¿å ¥ããŠã¿ãŸãããã
ããŒã¿ã1ä»¶ã ãæ¿å ¥ããå Žå
# ããŒã¿æ¿å
¥ã®ã¯ãšãª
insert_data_query = """
INSERT INTO users (username, email) VALUES (%s, %s)
"""
# ããŒã¿æ¿å
¥
user_data = ("Tanaka", "tanaka@example.com")
cursor.execute(insert_data_query, user_data)
# 倿Žã確å®
conn.commit()
ãã®ãµã³ãã«ã³ãŒãã§ã¯ã"users"ããŒãã«ã«æ°ãããŠãŒã¶ãŒã®ããŒã¿ã1ä»¶æ¿å ¥ããŠããŸãã%sã¯ãã¬ãŒã¹ãã«ããŒãšåŒã°ãããã®ã§ãåŸã§äžããããããŒã¿ã§çœ®ãæããããŸãã
è€æ°ä»¶ã®ããŒã¿ããŸãšããŠæ¿å ¥ããå Žå
è€æ°ä»¶ã®ããŒã¿ããŸãšããŠããªã¹ãã§æž¡ãããšãã§ããŸãã
# è€æ°ããŒã¿æ¿å
¥ã®ã¯ãšãª
insert_multiple_data_query = """
INSERT INTO users (username, email) VALUES (%s, %s), (%s, %s), (%s, %s)
"""
# è€æ°ããŒã¿æ¿å
¥
multiple_user_data = [
("Tanaka", "tanaka@example.com"),
("Yamada", "yamada@example.com"),
("Hashimoto", "hashimoto@example.com")
]
cursor.executemany(insert_multiple_data_query, multiple_user_data)
# 倿Žã確å®
conn.commit()
ãã®ãµã³ãã«ã³ãŒãã§ã¯ãäžåºŠã«è€æ°ã®ãŠãŒã¶ãŒã®ããŒã¿ã"users"ããŒãã«ã«æ¿å
¥ããŠããŸãã
å®è¡æã®ã¡ãœãããexecutemany()ã«ãªã£ãŠããããšã«ã泚æãã ããã
ããŒã¿ãååŸãã
æ¿å ¥ããããŒã¿ãååŸããŠã¿ãŸãããã
ããŒã¿ãäžä»¶ã ãååŸããå Žå
# ããŒã¿ååŸã®ã¯ãšãª
select_one_data_query = "SELECT * FROM users WHERE username = %s"
# ããŒã¿ååŸ
cursor.execute(select_one_data_query, ("Tanaka"))
# çµæãååŸ
result = cursor.fetchone()
# çµæã衚瀺
print(result)
ãã®ãµã³ãã«ã³ãŒãã§ã¯ã"users"ããŒãã«ãããŠãŒã¶ãŒåã"Tanaka"ã®ããŒã¿ãäžä»¶ååŸããŠããŸãã
ãã¹ãŠã®ããŒã¿ãååŸããŠç¹°ãè¿ãåŠçãããå Žå
# ããŒã¿ååŸã®ã¯ãšãª
select_all_data_query = "SELECT * FROM users"
# ããŒã¿ååŸ
cursor.execute(select_all_data_query)
# çµæãååŸ
result = cursor.fetchall()
# çµæã衚瀺
for row in result:
print(row)
ãã®ãµã³ãã«ã³ãŒãã§ã¯ã"users"ããŒãã«ã®ãã¹ãŠã®ããŒã¿ãååŸããforæã䜿çšããŠç¹°ãè¿ãåŠçãè¡ã£ãŠããŸãã
ããŒã¿ãæŽæ°ãã
# ããŒã¿æŽæ°ã®ã¯ãšãª
update_data_query = "UPDATE users SET email = %s WHERE username = %s"
# ããŒã¿æŽæ°
new_email = "Tanaka.new@example.com"
cursor.execute(update_data_query, (new_email, "Tanaka"))
# 倿Žã確å®
conn.commit()
ãã®ãµã³ãã«ã³ãŒãã§ã¯ã"users"ããŒãã«ã®ãŠãŒã¶ãŒåã"Tanaka"ã®ããŒã¿ã®ã¡ãŒã«ã¢ãã¬ã¹ãæŽæ°ããŠããŸãã
ããŒã¿ãåé€ãã
# ããŒã¿åé€ã®ã¯ãšãª
delete_data_query = "DELETE FROM users WHERE username = %s"
# ããŒã¿åé€
cursor.execute(delete_data_query, ("Tanaka"))
# 倿Žã確å®
conn.commit()
ãã®ãµã³ãã«ã³ãŒãã§ã¯ã"users"ããŒãã«ãããŠãŒã¶ãŒåã"Tanaka"ã®ããŒã¿ãåé€ããŠããŸãã
ãŸãšã
æ¬èšäºã§ã¯ãPythonã§MySQLããŒã¿ããŒã¹ã«æ¥ç¶ããŠåºæ¬çãªæäœãè¡ãæ¹æ³ã解説ããŸããã
ããŒã¿ããŒã¹ã®äœæãããŒãã«ã®äœæãããŒã¿ã®æ¿å
¥ã»ååŸã»æŽæ°ã»åé€ã¯WEBã¢ããªã±ãŒã·ã§ã³ãéçºããéçšã§é »ç¹ã«å®æœããåŠçã§ãã
ããã§è§£èª¬ããæé ãåºã«ãå®éã®ãããžã§ã¯ããã¢ããªã±ãŒã·ã§ã³ã§ããŒã¿ããŒã¹ã掻çšããŠã¿ãŸãããã












