Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Python MySQL database connection     - Making Linux root file system problems on-link library (Programming)

- Execute command sentence can result in equipment permanently bricked in Linux laptop (Linux)

- shell script: MySQL monitoring service is normal (Database)

- Repair fatal error in Linux: lame / lame.h: No such file or dir Error (Linux)

- Oracle Standby Redo Log experiment (Database)

- Linux host dual LAN transceiver package ARP problem (Linux)

- RAID configuration and management under linux (Server)

- SSL VPN SSL VPN access to security websites patron (Linux)

- Oracle conditional select statements and looping statements (Database)

- Memory leak analysis using Android studio (Programming)

- C ++ based socket communication TCP and UDP (Programming)

- TWiki LDAP error appears the problem is solved (Linux)

- Servlet 3.0 interfaces of AsyncListener (Programming)

- Linux using RAID how to use Mdadm Tool Management Software RAID (Linux)

- wget command usage Explanation (Linux)

- Android project using the command to create and install the package (Programming)

- [JavaScript] catch (ex) statements of ex (Programming)

- Use read command to read user input (Programming)

- Git Experience Sharing - Using a remote repository (Linux)

- Ubuntu 14.04 Docker installation (Linux)

 
         
  Python MySQL database connection
     
  Add Date : 2018-11-21      
         
         
         
  Establish a database connection import MySQLdb
conn = MySQLdb.connect (host = "localhost", user = "root", passwd = "sa", db = "mytable")

More commonly used parameters include
host: the database server host name to connect, the default is the local host (localhost).

user: the database login name defaults to the current user.
passwd:. login secret database is empty by default.
db: name of the database you want to use with no default.
port:. MySQL service uses TCP port 3306 by default.
conn connection There are two important ways to add and modify commit [submit], rollback [withdraw] to add or modify

3, perform database operations
n = cursor.execute (sql, param)
We want to use the connection object to get a cursor object Next, we'll use the cursor to provide work.
These include two categories: 1 Run 2 receives the return value.
cursor method is used to execute commands:
callproc (self, procname, args): used to execute a stored procedure, the received parameters for the stored procedure name and parameter list, the return value is the number of affected rows
execute (self, query, args): parameter performs a single sql statement sql statement itself is received and a list of parameters used, the return value is the number of affected rows
executemany (self, query, args): execute sql statement singled out, but repeated the argument list of parameters executed, the return value is the number of affected rows
nextset (self): move to the next result set
cursor for receiving return values:
fetchall (self): return receive all the result rows.
fetchmany (self, size = None): receiver size bar return result rows if the value is greater than the size of the number of results returned rows of data will be returned cursor.arraysize.
fetchone (self): return a result rows.
scroll (self, value, mode = 'relative'):. Move the pointer to a row if mode = 'relative', it means that the current line move value item, if mode = 'absolute', said the first results from the set line of mobile value bars.
The following code is a complete example.
# Using sql statement, where parameters are to be received by% s placeholder is to be noted that, whether you want to insert the data of what type, the placeholder should always use% s
sql = "insert into cdinfo values ​​(% s,% s,% s,% s,% s)"
#param be tuple or list
param = (title, singer, imgurl, url, alpha)
# Execution, if successful, n value of 1
n = cursor.execute (sql, param)
# Again to perform a query operation
cursor.execute ( "select * from cdinfo")

# Fetchall we use this method. Thus, cds will be stored in all the results returned by the query. Each result is a tuple type of data that make up a tuple tuple
cds = cursor.fetchall ()

# Because it is tuple, so we can use this result set
print cds [0] [3]
# Or directly displayed, look at the real appearance of the result set
print cds

# If you need to bulk insert data, to do so
sql = "insert into cdinfo values ​​(0,% s,% s,% s,% s,% s)"
# Set each value is a tuple, the entire parameter set consisting of a tuple, or list
param = ((title, singer, imgurl, url, alpha), (title2, singer2, imgurl2, url2, alpha2))
# Use executemany way to bulk insert data. This is a really cool way!
n = cursor.executemany (sql, param)

Note that (or that I am surprised) completes execution after the insertion or deletion or modification, you need to call about conn.commit () method to commit. Thus, the data will actually stored in the database. I unclear whether my mysql setup problems, in short, today I used at the beginning, if you do not commit, that data is not retained in the database, but the data in the database actually spent because automatic numbering has been accumulated and the number of rows affected is not returned to zero.

4, close the database connection
Close pointer need separate objects and connection objects. They have the same name method
cursor.close ()
conn.close ()

5 Coding (prevent distortion)

Note that point:

1 Python settings file encoding utf-8 (in front of the file plus # encoding = utf-8)
    2 MySQL database charset = utf-8
    3 Python MySQL connection is to add parameters charset = utf8
    4 Set Python's default encoding is utf-8 (sys.setdefaultencoding (utf-8)


# Encoding = utf-8

import sys

import MySQLdb

reload (sys)

sys.setdefaultencoding ( 'utf-8')

db = MySQLdb.connect (user = 'root', charset = 'utf8')
 

Note: MySQL configuration file settings must also be configured to utf8

 

6. The module Demo

[Root @ localhost python] # more mysql.py
#! / Usr / bin / python


import module import MySQLdb ###
conn = MySQLdb.connect (user = "root", passwd = "ESBecs00! @ #", db = "test") ### connected to mysql
cursor = conn.cursor () ### a link cursor


sql = "select * from test" ### sql statement
n = cursor.execute (sql) ### execute sql
print n ### is executed successfully returns n (n represents the number of impacts, which is 3)

row = cursor.fetchall () ### fetchall () Returns all live results, fetchone () returns a live result
print row ### to print out the return results


print "################################################ #########Dividing line####################################### ########### "


cursor.execute ( '' '### can also be placed directly inside the sql statement execution
create table if not exists food (
id int (10) primary key,
name varchar (20),
age int (10)
)
'' ')
sql = "desc food"
cursor.execute (sql)
row = cursor.fetchall ()
print row

 

cursor.close () ### Close the cursor
conn.close () ### closes the connection

 


Results of the

[Root @ localhost python] # python mysql.py
3
(( 'Zhangsan',), ( 'lisi',), ( 'wangwu',))
################################################## #######
mysql.py:21: Warning: Table 'food' already exists
  '' ')
(( 'Id', 'int (10)', 'NO', 'PRI', None, ''), ( 'name', 'varchar (20)', 'YES', '', None, '' ), ( 'age', 'int (10)', 'YES', '', None, '')
 

 

Execute the following SQL statement to capture the return value:
// Get the cursor connection
cursor = conn.cursor ()
//Inquire
sql = "select * from [table]."
cursor.execute (sql)

return value
cur.execute ( 'select * from tables')
Returns the number of rows to get the value of SQL statements, such as: 2L, represents a row.
Then, line information can be obtained from fetchone or fetchall method of that object.

Get line information
fetchone object pointer () method, is to give each row tuple return value:
Quote
>>> Row = cur.fetchone ()
>>> Print row
( 'User1', '52c69e3a57331081823331c4e69d3f2e', 1000L, 1000L, '/ home / FTP / user1', '')

fetchall object pointer () method, you can remove all rows in the result set pointer, the result set returns a tuple (tuples):
Quote
>>> Cur.scroll (0, 'absolute')
>>> Row = cur.fetchall ()
>>> Print row
(( 'User1', '52c69e3a57331081823331c4e69d3f2e', 1000L, 1000L, '/ home / FTP / user1', ''), ( 'user2', '7e58d63b60197ceb55a1c487989a3720', 1000L, 1000L, '/ home / FTP / user2', None ))

Move the pointer
When fetchone () method, the pointer is moved occur. So, if not reset the pointer, use fetchall information will only contain the line pointer back.
Manually move the pointers:
cur.scroll (int, parm)
Meaning:
Quote
int: The number of lines is an integer; in relative mode, positive move downwards, negative values ​​indicate movement upward.
parm: moving mode, the default is relative, relative mode; acceptable absoulte, absolute mode.

Modify data
Modify data, including insert, update, delete. They are using a pointer object execute () method is executed:
cur.execute ( "insert into table (row1, row2) values ​​( '111', '222')")
cur.execute ( "update table set row1 = 'test' where row2 = 'row2'")
cur.execute ( "delete from table where row1 = 'row1'")

Because single quote " '" is used to identify the SQL statement, therefore, python string need to use double quotes.
Alternatively, you can use the python "format string" writing, simplified command, for example:
cur.execute ( "update table set row1 = '% s' where row2 = '% s'"% ( 'value1', 'value2'))

Please note that, '% s' single quotation marks are spaced characters SQL statements, 'value1' single quotation marks are python string break character, its meaning is different. Need break character, and use double quotes or single quotes as a spacer, to be determined according to its meaning. For example, there are:
cur.execute ( "update FTPUSERS set passwd =% s where userid = '% s'"% ( "md5 ( '123')", 'user2'))
Here, paswd =% s is due to SQL's md5 () function does not require single quotes interval; "md5 ( '123')" is a string containing a python in single quotes, double quotes around it.

Submit modification
Under normal circumstances, MySQLdb module automatically commit changes. But after we update data manually run again:
conn.commit ()

Close the database connection
Close pointer need separate objects and connection objects. They have the same name method
cursor.close ()
conn.close ()
     
         
         
         
  More:      
 
- Ubuntu 14.04 LTS 64-bit install GNS3 1.3.7 (Linux)
- The most concise explanation of JavaScript closures (Programming)
- Shell for loop (Programming)
- Linux system crash (no such file or directory) How to rescue database (Linux)
- Copy and paste in Linux terminal and Vim (Linux)
- Use Ambari rapid deployment Hadoop big data environment (Server)
- DBCA Error: ORA-19809: limit exceeded for recovery files process (Database)
- ActiveMQ5.11.1 and JDK version matching relation (Linux)
- Customize own small private Linux system (Linux)
- Elasticsearch Kibana installation notes (Linux)
- Configuring Android NDK development environment for Mac OS X (Linux)
- MySQL binlog automatic cleanup script (Database)
- See Shell Script Linux Server network traffic (Server)
- 10 useful Linux command Interview Questions and Answers (Linux)
- Math objects easily overlooked but very convenient method --JavaScript (Programming)
- Create a DLL using MinGW and Attention (Programming)
- Cool Android realization SVG animation (Programming)
- Spring REST Exception Handling (Programming)
- JavaScript object - Flexible and dangerous (Programming)
- Java object serialization (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.