- mysql 기초
SQL Tutorial
SQL Tutorial SQL is a standard language for storing, manipulating and retrieving data in databases. Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems. Start learn
www.w3schools.com
mysql -u root -p
show databases;
use bookstore;
show tables;
insert into books (id,title, author) values(2,'python book', '이미자');
select id, title, author from books;
select id, title, author from books where id=2;
select id, title, author from books where author like'이%';
- python 연동
pymysql
mysqlclient
pip install mysqlclient
import MySQLdb
def db_select():
""" 접속테스트 """
# 접속
connect = MySQLdb.connect(
user='root',
password='1234',
host='localhost', #127.0.0.1
db='bookstore',
charset='utf8')
cursor = connect.cursor()
sql = 'select id, title, author from books'
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.close()
connect.close()
if __name__ == "__main__":
db_select()
참고)
import MySQLdb
def db_select():
""" 접속테스트 """
# 접속
connect = MySQLdb.connect(
user='root',
password='1234',
host='localhost', #127.0.0.1
db='bookstore',
charset='utf8')
cursor = connect.cursor()
sql = 'select id, title, author from books'
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.close()
connect.close()
def db_insert():
""" insert """
# 접속한다
connect = MySQLdb.connect(
user='root',
password='1234',
host='localhost',
db='bookstore',
charset='utf8')
cursor = connect.cursor()
title = 'machine learning'
author = '이순자'
sql = f"insert into books(title, author) values('{title}', '{author}')"
cursor.execute(sql)
connect.commit() # db에 반영
cursor.close()
connect.close()
db_insert()
- dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchmany.html
MySQL :: MySQL Connector/Python Developer Guide :: 10.5.7 MySQLCursor.fetchmany() Method
10.5.7 MySQLCursor.fetchmany() Method Syntax: rows = cursor.fetchmany(size=1) This method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list. The number of rows returned can
dev.mysql.com
import MySQLdb
class Connect:
def __init__(self):
self.connect = MySQLdb.connect(
user='root',
password='1234',
host='localhost', # 127.0.0.1
db='bookstore',
charset='utf8')
self.cursor = self.connect.cursor()
while True:
con = Connect()
print('1: 전체 조회 2: 등록 7:건수 표시 9: 종료 ', end='')
choice = input()
choice = int(choice)
# 전체 조회
if choice == 1:
sql = 'select id, title, author from books'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
print(row)
# 등록
if choice == 2:
title = input('제목 입력: ')
author = input('저자명 입력: ')
sql = f"insert into books(title, author) values('{title}', '{author}')"
con.cursor.execute(sql)
con.connect.commit()
#건수 표시
if choice == 7:
sql = 'select count(*) from books'
con.cursor.execute(sql)
(count, ) = con.cursor.fetchone()
print('전체 건수 =', count)
#종료
if choice == 9:
exit(0)
- 쌤 코드
import MySQLdb
class Connect:
def __init__(self):
self.connect = MySQLdb.connect(
user = 'root',
password = '1234',
host = 'localhost',
db = 'bookstore',
charset = 'utf8')
self.cursor = self.connect.cursor()
while True:
con = Connect()
print ('1: 전체 조회 2: 등록 3: 업데이트 4: 삭제 5: 제목 유사검색 7: 건수 표시 9: 종료 ', end='')
choice = input()
choice = int(choice)
if choice == 1:
sql = 'select id, title, author from books'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
print(row)
if choice == 2:
title = input('제목 입력 ')
author = input('저자명 입력 ')
sql = f"insert into books(title, author) values('{title}', '{author}')"
affected_rows = con.cursor.execute(sql)
if affected_rows == 1:
print('정상 등록 되었음')
con.connect.commit()
if choice == 3: # 업데이트
sql = 'select id, title, author from books'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
(id, title, author) = row
print(f'{id}, {title}, {author}')
id = input('업데이트할 id 입력 ')
id = int(id)
sql = f'select id, title, author from books where id={id}'
con.cursor.execute(sql)
(id, title, author) = con.cursor.fetchone()
print(id, title, author)
title = input('변경할 제목 입력 ')
author = input('변경할 저자명 입력 ')
sql = f"update books set title = '{title}', author='{author}' where id={id}"
affeted_rows = con.cursor.execute(sql)
if affeted_rows == 1:
print('정상 변경 되었음')
con.connect.commit()
if choice == 4: # 삭제
sql = 'select id, title, author from books'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
(id, title, author) = row
print(f'{id}, {title}, {author}')
id = input('삭제할 id 입력 ')
id = int(id)
sql = f'delete from books where id={id}'
affeted_rows = con.cursor.execute(sql)
if affeted_rows == 1:
print('정상 삭제 되었음')
con.connect.commit()
if choice == 5: # 제목 유사검색
title = input('유사 검색할 제목 입력 ')
sql = f"select id, title, author from books where title like '{title}%'"
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
(id, title, author) = row
print(f'{id}, {title}, {author}')
if choice == 7:
sql = 'select count(*) from books'
con.cursor.execute(sql)
(total_count, ) = con.cursor.fetchone()
print('전체 건수=', total_count)
if choice == 9:
exit(0)- 쌤꺼 보면서 내 코드 수정함
import MySQLdb
class Connect:
def __init__(self):
self.connect = MySQLdb.connect(
user='root',
password='1234',
host='localhost', # 127.0.0.1
db='bookstore',
charset='utf8')
self.cursor = self.connect.cursor()
while True:
con = Connect()
print('1: 전체 조회 2: 등록 3: 업데이트 4: 삭제 5: 유사검색 7:건수 표시 9: 종료 ', end='')
choice = input()
choice = int(choice)
# 전체 조회
if choice == 1:
sql = 'select id, title, author from books'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
(id_, title, author) = row
print(f'{id_}, {title}, {author}')
# 등록
if choice == 2:
title = input('제목 입력: ')
author = input('저자명 입력: ')
sql = f"insert into books(title, author) values('{title}', '{author}')"
con.cursor.execute(sql)
con.connect.commit()
# 업데이트
if choice == 3:
sql = 'select id, title, author from books'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
print(row)
id_ = input('업데이트할 id 입력: ')
sql = f'select id, title, author from books where id={id_}'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
(id_, title, author) = row
print(f'{id_}, {title}, {author}')
title = input('업데이트 할 제목 입력: ')
author = input('업데이트 할 저자명 입력: ')
sql = f"update books set title = '{title}', author = '{author}' where id = '{id_}'"
affeted_rows = con.cursor.execute(sql)
if affeted_rows == 1:
print("업데이트 되었습니다")
con.connect.commit()
# 삭제
if choice == 4:
sql = 'select id, title, author from books'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
(id_, title, author) = row
print(f'{id_}, {title}, {author}')
id_ = input('삭제할 id 입력: ')
sql = f'select id, title, author from books where id={id_}'
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
(id_, title, author) = row
print(f'{id_}, {title}, {author}')
dele = input('삭제하시겠습니까?: y/n')
print(dele)
if dele == "y":
sql = f"delete from books where id = '{id_}'"
print("삭제되었습니다")
con.cursor.execute(sql)
con.connect.commit()
# 유사검색
if choice == 5:
title = input('유사검색할 제목 입력: ')
sql = f"select id, title, author from books where title LIKE '%{title}%'"
con.cursor.execute(sql)
rows = con.cursor.fetchall()
for row in rows:
(id_, title, author) = row
print(f'{id_}, {title}, {author}')
#건수 표시
if choice == 7:
sql = 'select count(*) from books'
con.cursor.execute(sql)
(total_count, ) = con.cursor.fetchone()
print('전체 건수 =', total_count)
#종료
if choice == 9:
exit(0)