Shulou(Shulou.com)06/03 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.