This is the 25th day of my participation in Gwen Challenge
Search “Program Yuan Xiaozhuang” on wechat, and you will get a full set of Python full stack tutorials, as well as the constantly updated e-books and interview materials prepared by Xiaozhuang
preface
MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL
Introduction to pymysql module
Pymysql is a third party module in Python that provides a way to connect to mysql using Python so that you can connect to and manipulate the database using code.
The installation of the Pymysql module is also very simple, from the CMD command line:
Install the latest version by default
pip install pymysql
Copy the codeBasic use of the Pymysql module
Pymysql is a third-party module, which needs to be imported when used. For details about how to use Pymysql to add, delete, change and check data, please see the following code:
import pymysql
Connect to database
conn = pymysql.connect(
user='root'.MySQL > log in to MySQL as user name
password='1026'.# your password
host='127.0.0.1'.127.0.0.1 is the IP address of MySQL
port=3306.# MySQL port 3306 if not modified
database='book_manage'.The name of the database to operate on
charset='utf8mb4'.# database character set
autocommit=True True indicates that the data is automatically committed
)
Get the cursor object used to execute the SQL statement
cursor = conn.cursor(pymysql.cursors.DictCursor)
# increment -- the first method is execute
sql = 'insert into author(name, age) values ("python", 10), ("java", 5); '
rows = cursor.execute(sql) # rows is the number of rows affecting the database
# increment -- the second method is execute+ string formatting
sql = 'insert into author(name, age) values (%s, %s); '
rows = cursor.execute(sql, ('go'.5))
# increment -- The third way to increment multiple numbers is executemany
sql = 'insert into author(name, age) values (%s, %s); '
rows = cursor.executemany(sql, [('php'.10), ('ruby'.15), ('js'.20)])
# If autoCOMMIT =True is not specified when establishing the connection, manual commit is required
conn.commit()
# modify data
sql = 'update author set name = %s where id = %s; '
# a instead
rows = cursor.execute(sql, ('haha'.2))
# change more
rows = cursor.executemany(sql, [('haha'.5), ('xxx'.3)])
# delete data
sql = 'delete from author where name = %s and id = %s; '
Execute a delete SQL
rows = cursor.execute(sql, ('xxx'.3))
# execute multiple delete SQL
rows = cursor.executemany(sql, [('haha'.2), ('haha'.5)])
# query data
sql = 'select * from author; '
rows = cursor.execute(sql)
# fetchall retrieves all results of the query
result = cursor.fetchall()
print(result)
# fetchOne fetch the first query result
result1 = cursor.fetchone()
print(result1)
# fetchmany retrieves the first n rows in the query result
result2 = cursor.fetchmany(3)
Copy the codeBased on pymysql module to achieve login and registration
Before if you want to complete the user login and registration function can only through the file, with MySQL can be stored in the database, without the use of file storage, the following is based on pymysql module database operation to achieve the login and registration function.
import pymysql
class MyDBHandle:
def __init__(self) :
self.conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='123456',
db='userinfo',
charset='utf8',
autocommit=True
)
self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
def _db_handle_select(self, *args) :
sql = 'select * from user where username=%s'
res = self.cursor.execute(sql, args)
if not res:
return {}
else:
return self.cursor.fetchone()
def _db_handle_insert(self, *args) :
sql = 'insert into user(username, password) values(%s, %s)'
self.cursor.execute(sql, args)
def login_interface(self, name, pwd) :
user_dict = self._db_handle_select(name)
if not user_dict:
return False.'Username does not exist'
else:
ifpwd ! = user_dict.get('password') :return False.'Wrong username or password'
return True.'Login successful'
def register_interface(self, name, pwd) :
user_dict = self._db_handle_select(name)
if user_dict:
return False.'Username already exists'
self._db_handle_insert(name, pwd)
return True.'Registration successful'
class MyTest:
def __init__(self, db_handle_obj) :
self.db_handle = db_handle_obj
def run(self) :
while 1:
cmd_list = [('login'.'login'), ('registered'.'register')]
for index, item in enumerate(cmd_list):
print(index, item[0])
cmd = input('Please enter the function number:').strip()
if not cmd.isdigit() or int(cmd) not in range(len(cmd_list)):
continue
cmd_func = cmd_list[int(cmd)][1]
if hasattr(self, cmd_func):
func = getattr(self, cmd_func)
func()
def login(self) :
while 1:
username = input('username>>:').strip()
password = input('password>>:').strip()
flag, msg = self.db_handle.login_interface(username, password)
print(msg)
if flag: break
def register(self) :
while 1:
username = input('username>>:').strip()
password = input('password>>:').strip()
re_pwd = input('re_pwd>>:').strip()
ifre_pwd ! = password:print('Two different passwords entered')
continue
flag, msg = self.db_handle.register_interface(username, password)
print(msg)
if flag: break
if __name__ == '__main__':
test = MyTest(MyDBHandle())
test.run()
Copy the codeconclusion
The article was first published on the wechat public account Program Yuan Xiaozhuang, and synchronized with nuggets and Zhihu.
The code word is not easy, reprint please explain the source, pass by the little friends of the lovely little finger point like and then go (╹▽╹)
