Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Python MySQL database connection     - Repair fatal error in Linux: lame / lame.h: No such file or dir Error (Linux)

- Analysis of potential problems through custom Orabbix monitoring Oracle (Database)

- NFS-based services and service utilization Corosync DRBD high availability cluster configuration, respectively (Server)

- Linux Bash share tips for getting started (Linux)

- System-level alias vs Oracle ADR functionality (Database)

- Five strokes to find out the IP address you want to know (Linux)

- Recover Ubuntu 14.04 wakes up from standby in case mouse keyboard appears dead (Linux)

- Upgrading to MySQL 5.7 partitioning problem solving (Database)

- Windows7 system using Vagrant to build Linux virtualized development environment (Linux)

- Jigsaw project will solve the problem of Java JAR hell Mody (Programming)

- Delete specific files using bash directory under Linux (Linux)

- iOS in the event delivery and the responder chain (Programming)

- Docker commonly used commands Description (Linux)

- Installation of Gitlab under Ubuntu (Linux)

- Configuring Android NDK development environment for Mac OS X (Linux)

- Close Pycharm spell check (Programming)

- Linux operating system boot process analysis (Linux)

- Linux memory Cache Analysis (Linux)

- Singleton (Linux)

- LVM basic concepts, management (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:      
 
- Java Performance Tuning the proxy mode (Programming)
- stat - Get more information than ls (Linux)
- Ubuntu / Fedora / CentOS system how to install Plex Media Server 0.9.9 (Linux)
- Redis is installed in Ubuntu 14.04 (Database)
- Mongo-connector integrated MongoD to achieve incremental Solr index (Server)
- To install Spotify in Ubuntu / Mint (Linux)
- Let Ubuntu 14.04 Save screen luminance value (Linux)
- Getting Started with Linux system to learn: how to install USB webcams come in raspberry (Linux)
- Android memory optimization of the memory cache (Linux)
- Ubuntu install perfectly handsome terminal Guake 0.8.1 (Linux)
- Spring Integration ehcache annotation implement the query cache and cache update or delete instant (Programming)
- How to enhance the Nagios server security (Linux)
- Comparison of one-time transaction and CTE insert data (Database)
- Ubuntu 14.04 How to install Cinnamon 2.4.5 (Linux)
- Oracle 11R2 Grid Infrastructure execute root.sh script rootcrs.pl execution failed treatment (Database)
- Getting Started with Linux system to learn: how to use tcpdump to capture TCP SYN, ACK and FIN packets (Linux)
- Oracle PL / SQL based cursor (Database)
- How to contribute code on GitHub uploads (Linux)
- The Java utility, JavaMail (Programming)
- How to install the Linux text editor Atom 0.124.0 (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.