How to use pyMySql to connect to mysql
This article introduces the knowledge of "how to use pyMySql to connect mysql". In the operation of practical cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
Installation
Pip3 install pymysql
Common functions:
Execute () executes the statement and returns the number of rows affected by the collection
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
# execute SQL and return the number of rows affected by revenue
Effect_row = cursor.execute ("insert into T2 VALUES (1m)")
# execute SQL and return the number of affected rows
# effect_row = cursor.execute ("update hosts set host = '1.1.1.2' where nid >% s", (1,))
# execute SQL and return the number of affected rows
# effect_row = cursor.executemany ("insert into hosts (host,color_id) values (% s)", [("1.1.1.11", 1), ("1.1.1.11", 2)])
# submit, otherwise the newly created or modified data cannot be saved
Conn.commit ()
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
# execute SQL and return the number of rows affected by revenue
Truple_str='ray'
Effect_row = cursor.execute ("insert into T2 VALUES (3Jing% s)", truple_str)
Print (effect_row)
Truple_str='suen'
Effect_row = cursor.execute ("insert into T2 VALUES (4Jing% s)", truple_str)
Print (effect_row)
# submit, otherwise the newly created or modified data cannot be saved
Conn.commit ()
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
# execute SQL and return the number of rows affected by revenue
Truple_str= ('lalala',1)
Cursor.execute ('update T2 set t_name=%s where tweeds% swatches, realism, etc.)
# submit, otherwise the newly created or modified data cannot be saved
Conn.commit ()
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
# execute SQL and return the number of rows affected by revenue
# truple_str= ('lalala',1)
Tid=1
Cursor.execute ('delete from T2 where tweeds% swords relegation tid)
# submit, otherwise the newly created or modified data cannot be saved
Conn.commit ()
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Executemany () executes a statement, passing in as a tuple
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
# execute SQL and return the number of rows affected by revenue
Truple_str= [
(5 minutes aaaaa')
(6 minute bbbb')
(7 minute cccc')
(8 ~ dd')
]
Cursor.executemany ("insert into T2 VALUES (% s)", truple_str)
# submit, otherwise the newly created or modified data cannot be saved
Conn.commit ()
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Fecheone ()
Fechemany ()
Fecheall ()
Note: when fetch data is in order, you can use cursor.scroll (num,mode) to move the cursor position, such as:
Cursor.scroll (1) # moves relative to the current position, with positive numbers moving down and negative numbers moving up
Cursor.scroll (2) # moves relative to the absolute position
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
# execute SQL and return the number of rows affected by revenue
# truple_str= ('lalala',1)
Cursor.execute ('select * from T2')
# fech is obtained from memory
Res = cursor.fetchone ()
Print (res)
Res = cursor.fetchmany (7) # specify the number of entries obtained
Print (res)
Res = cursor.fetchall ()
Print (res)
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
# execute SQL and return the number of rows affected by revenue
# truple_str= ('lalala',1)
Cursor.execute ('select * from T2')
# fech is obtained from memory
Res = cursor.fetchone ()
Print (res)
Res = cursor.fetchone ()
Print (res)
Cursor.scroll (0thecontrolling)
Res = cursor.fetchall ()
Print (res)
Cursor.scroll (- 1)
Res = cursor.fetchall ()
Print (res)
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
# execute SQL and return the number of rows affected by revenue
# truple_str= ('lalala',1)
Cursor.execute ('select * from T2')
# fech is obtained from memory
While 1:
Res = cursor.fetchone ()
If res = = None:
Break
Print (res)
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Modified by cursor, the obtained result will be a dictionary collection.
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor= conn.cursor (cursor=pymysql.cursors.DictCursor)
# execute SQL and return the number of rows affected by revenue
# truple_str= ('lalala',1)
Cursor.execute ('select * from T2')
# fech is obtained from memory
While 1:
Res = cursor.fetchone ()
If res = = None:
Break
Print (res)
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
Lastrowid gets the last self-incrementing serial number
Import pymysql
# create a connection
Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='123456', db='test',charset= "utf8")
# create cursors
Cursor = conn.cursor ()
Cursor.executemany ('insert into T3 (t_name) VALUES (% s)', [('aaa07'), (' aaa08'), ('aaa09')])
Conn.commit ()
Tid=cursor.lastrowid
Print (tid)
# close cursors
Cursor.close ()
# close the connection
Conn.close ()
This is the end of "how to use pyMySql to connect to mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
