๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
IT-Engineering/TroubleShooting

Windowsํ™˜๊ฒฝ์—์„œ Python mysql ์ ‘์† ๋ฌธ์ œ ํ•ด๊ฒฐ (Alchemy, MySQLdb ์‚ฌ์šฉ์‹œ)

by ๐Ÿงž‍โ™‚๏ธ 2020. 9. 3.
๋ฐ˜์‘ํ˜•

์ด๋ฒˆ ๊ฒฝ์šฐ๋Š” ์•„๋ž˜์˜ ์ฝ”๋“œ์ฒ˜๋Ÿผ pandas์˜ DataFrame๊ฐ์ฒด๋ฅผ mysql db์— ์‚ฝ์ž…ํ•˜๋Š” ๊ณผ์ •์—์„œ ๋ฐœ์ƒํ•œ ์—๋Ÿฌ์˜€๋‹ค.

engine = create_engine('mysql+mysqlconnector://'+MYSQL_USER+':'+MYSQL_PASSWORD+'@'+MYSQL_HOST_IP+':'+MYSQL_PORT+'/'+MYSQL_DATABASE, echo=False)
df = pd.read_csv("./test.csv")
with engine.connect() as conn, conn.begin():
    df.to_sql('Table1', conn, if_exists='replace')

์—๋Ÿฌ ๋‚ด์šฉ์€ ๊ฐœ๋žต์ ์œผ๋กœ ์•„๋ž˜์™€ ๊ฐ™๋‹ค. 

MySQLdb._exceptions.OperationalError: (2059, <NULL>)

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2059, <NULL>)

์œ„์™€ ๊ฐ™์€ ํ˜•ํƒœ๋กœ ๋‚˜ํƒ€๋‚˜๋Š” ์—๋Ÿฌ์˜ ๋ณธ์งˆ์ ์ธ ๋ฌธ์ œ๋Š” ๊ฐ™๋‹ค. ์—๋Ÿฌ๊ฐ€ ์–ด๋””์„œ ๋ฐœ์ƒํ–ˆ๋Š”์ง€ ์—๋Ÿฌ ์Šคํƒ์„ ์ถœ๋ ฅํ•˜๋‹ค๋ณด๋‹ˆ ์ด๋ ‡๊ฒŒ ์‚ฌ๋žŒ์— ๋”ฐ๋ผ ๋‹ค๋ฅธ ๋ถ€๋ถ„์„ ๋ณผ ์ˆ˜ ์žˆ์–ด์„œ ๊ฒ€์ƒ‰์˜ ์šฉ์ด์„ฑ์„ ์œ„ํ•ด ๋‘˜๋‹ค ๊ฐ€์ ธ๋‹ค ๋†“์•˜๋‹ค.

Windows์—์„œ mysql์„ ๊ธฐ๋ณธ ์„ค์ •์œผ๋กœ ์„ค์น˜ํ•˜๋‹ค๋ณด๋ฉด ์•”ํ˜ธ ์ €์žฅ์˜ ๋ณด์•ˆ์„ ๊ฐ•ํ™”ํ•˜๋Š” ์˜ต์…˜์œผ๋กœ ์„ค์น˜ํ•˜๊ฒŒ ๋œ๋‹ค. ์ด ๋ถ€๋ถ„์˜ ๋ณ€๊ฒฝ์ด ํ•„์š”ํ•˜๋‹ค.

MySQL Command Line Client์„ ์‹คํ–‰ํ•œ ํ›„ ๋‹ค์Œ์˜ ๋ช…๋ น์–ด๋ฅผ ์ž…๋ ฅํ•ด๋ณด์ž.

use mysql;
select user,host,plugin from user;

์œ„ ๋ช…๋ น์–ด๋ฅผ ์ž…๋ ฅํ•˜๊ฒŒ ๋˜๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋ฐฉ์‹์œผ๋กœ ๊ณ„์ •์ด ์ƒ์„ฑ๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

์—ฌ๊ธฐ์„œ ๋ฐ”๋กœ 'caching_sha2_password'์ด๋ผ๋Š” sha์•”ํ˜ธํ™”๊ฐ€ ๋ฌธ์ œ์˜ ๊ทผ์›์ด๋‹ค.

์ด ๋ถ€๋ถ„์„ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” ์•”ํ˜ธํ™”์—†๋Š” ์ผ๋ฐ˜์ ์ธ ํ˜•ํƒœ๋กœ ๊ณ„์ • ์†์„ฑ์„ ๋ณ€๊ฒฝํ•ด์•ผ ํ•œ๋‹ค. ๋งˆ์ฐฌ๊ฐ€์ง€๋กœ MySQL Command Line Client ์—์„œ ์•„๋ž˜ ๋ช…๋ น์–ด๋ฅผ ์ˆ˜ํ–‰ํ•ด์ฃผ์ž.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword'

์ด์ œ ๋‹ค์‹œ ์ž…๋ ฅํ•ด๋ณด๋ฉด 

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

์œ„์™€ ๊ฐ™์ด ๋ณ€๊ฒฝ๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.

์ด ์ƒํƒœ๋กœ ๊ธฐ์กด์˜ ํŒŒ์ด์ฌ ์ฝ”๋“œ๋ฅผ ์žฌ์‹คํ–‰์‹œํ‚ค๋ฉด ์ •์ƒ ๋™์ž‘ ํ•œ๋‹ค. ๋ณ„ ๊ฒƒ ์•„๋‹Œ '๋‹ค์Œ' ๋ฒ„ํŠผ ํด๋ฆญ ํ•˜๋‚˜๊ฐ€ ๋˜ ํ•œ๋ฒˆ ์• ๋ฅผ ๋จน์˜€๋‹ค. ์  ์žฅํ• .

 

๋ฐ˜์‘ํ˜•

๋Œ“๊ธ€