python连接mysql语句

最新推荐文章于 2020-12-06 14:11:59 发布

cuansiye8661

最新推荐文章于 2020-12-06 14:11:59 发布

阅读量122

点赞数

python连接mysql

import mysql.connector
cnx = mysql.connector.connect(user='scott', password='tiger',
host='127.0.0.1',
database='employees')
cnx.close()

from mysql.connector import (connection)
cnx = connection.MySQLConnection(user='scott', password='tiger',
host='127.0.0.1',
database='employees')

import mysql.connector
from mysql.connector import errorcode
try:
cnx = mysql.connector.connect(user='scott',
database='testt')
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
else:
cnx.close()

import mysql.connector
config = {
'user': 'scott',
'password': 'tiger',
'host': '127.0.0.1',
'database': 'employees',
'raise_on_warnings': True,
}
cnx = mysql.connector.connect(**config)
cnx.close()

用Python 插入数据

from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector
cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()
tomorrow = datetime.now().date() + timedelta(days=1)
add_employee = ("INSERT INTO employees "
"(first_name, last_name, hire_date, gender, birth_date) "
"VALUES (%s, %s, %s, %s, %s)")
add_salary = ("INSERT INTO salaries "
"(emp_no, salary, from_date, to_date) "
"VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")
data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))
# Insert new employee
cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid
# Insert salary information
data_salary = {
'emp_no': emp_no,
'salary': 50000,
'from_date': tomorrow,
'to_date': date(9999, 1, 1),
}
cursor.execute(add_salary, data_salary)
# Make sure data is committed to the database
cnx.commit()
cursor.close()
cnx.close()

用python查询数据

import datetime
import mysql.connector
cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()
query = ("SELECT first_name, last_name, hire_date FROM employees "
"WHERE hire_date BETWEEN %s AND %s")
hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)
cursor.execute(query, (hire_start, hire_end))
for (first_name, last_name, hire_date) in cursor:
print("{}, {} was hired on {:%d %b %Y}".format(
last_name, first_name, hire_date))
cursor.close()
cnx.close()

附录：连接字符串的参数

Argument Name	Default	Description
`user` (`username`*)		The user name used to authenticate with the MySQL server.
`password` (`passwd`*)		The password to authenticate the user with the MySQL server.
`database` (`db`*)		The database name to use when connecting with the MySQL server.
`host`	127.0.0.1	The host name or IP address of the MySQL server.
`port`	3306	The TCP/IP port of the MySQL server. Must be an integer.
`unix_socket`		The location of the Unix socket file.
`auth_plugin`		Authentication plugin to use. Added in 1.2.1.
`use_unicode`	`True`	Whether to use Unicode.
`charset`	`utf8`	Which MySQL character set to use.
`collation`	`utf8_general_ci`	Which MySQL collation to use.
`autocommit`	`False`	Whether to autocommit transactions.
`time_zone`		Set the `time_zone` session variable at connection time.
`sql_mode`		Set the `sql_mode` session variable at connection time.
`get_warnings`	`False`	Whether to fetch warnings.
`raise_on_warnings`	`False`	Whether to raise an exception on warnings.
`connection_timeout`(`connect_timeout`*)		Timeout for the TCP and Unix socket connections.
`client_flags`		MySQL client flags.
`buffered`	`False`	Whether cursor objects fetch the results immediately after executing queries.
`raw`	`False`	Whether MySQL results are returned as is, rather than converted to Python types.
`ssl_ca`		File containing the SSL certificate authority.
`ssl_cert`		File containing the SSL certificate file.
`ssl_key`		File containing the SSL key.
`ssl_verify_cert`	`False`	When set to `True`, checks the server certificate against the certificate file specified by the `ssl_ca` option. Any mismatch causes a `ValueError` exception.
`force_ipv6`	`False`	When set to `True`, uses IPv6 when an address resolves to both IPv4 and IPv6. By default, IPv4 is used in such cases.
`dsn`		Not supported (raises `NotSupportedError` when used).
`pool_name`		Connection pool name. Added in 1.1.1.
`pool_size`	5	Connection pool size. Added in 1.1.1.
`pool_reset_session`	`True`	Whether to reset session variables when connection is returned to pool. Added in 1.1.5.
`compress`	`False`	Whether to use compressed client/server protocol. Added in 1.1.2.
`converter_class`		Converter class to use. Added in 1.1.2.
`fabric`		MySQL Fabric connection arguments. Added in 1.2.0.
`failover`		Server failover sequence. Added in 1.2.1.
`option_files`		Which option files to read. Added in 2.0.0.
`option_groups`	`['client', 'connector_python']`	Which groups to read from option files. Added in 2.0.0.
`allow_local_infile`	`True`	Whether to enable `LOAD DATA LOCAL INFILE`. Added in 2.0.0.
`use_pure`	`True`	Whether to use pure Python or C Extension. Added in 2.1.1.

常见错误：
1. 连接mysql错误： mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost' (111)
python脚本：

__author__ = 'root'
import mysql.connector
import locale
locale.setlocale(locale.LC_ALL,'C')
logMysql = mysql.connector.connect(host='localhost',port='1333',user='root',password='Pingan@phzc12345678',database='log');
logMysql.close();

执行后报错：

/usr/bin/python /root/PycharmProjects/untitled/testMysql.py
Traceback (most recent call last):
File "/root/PycharmProjects/untitled/testMysql.py", line 8, in <module>
logMysql = mysql.connector.connect(host='localhost',port='1333',user='root',password='Pingan@phzc12345678',database='log');
File "/usr/lib/python2.6/site-packages/mysql/connector/__init__.py", line 44, in Connect
return MySQLConnection(*args, **kwargs)
File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 108, in __init__
self.connect(**kwargs)
File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 356, in connect
self._open_connection()
File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 319, in _open_connection
self._socket.open_connection()
File "/usr/lib/python2.6/site-packages/mysql/connector/network.py", line 393, in open_connection
errno=2003, values=(self.server_host, msg))
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost

解决：在 mysql . connector . connect 中增加参数：unix_socket='/tmp/mysql.sock'，即 my.cnf 中 socket 配置对应的值；

2 插入数据
    执行插入操作后，没有任何报错提示，但是数据就是没有插入到相应的表中。
解决：
是由于少些了两行代码，没有提交和关闭游标
      logMysql.commit();
cursor.close();

addBackupLog=(
"INSERT INTO log_innobackupex(BACKUP_TYPE, DIR_NAME, BACKUP_STATUS, BACKUP_DIR, TAR_STATUS, TAR_DIR, SCP_STATUS, SCP_DIR) "
"VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
);
dataBackupLog=('FULL',max_dir_name, str(backIsSucc),directory+max_dir_name,str(tar_cmd),
directory+'tar_full/'+max_dir_name+'.tar.gz',str(scp_status),scp_server)
"""
addBackupLog=(
"INSERT INTO log_innobackupex(BACKUP_TYPE) "
"VALUES(%s)"
);
dataBackupLog=('FULL')
"""
try:
logMysql = mysql.connector.connect(user='****',password='********',host='localhost',database='log',port='3306',unix_socket='/tmp/mysql.sock',charset='utf8');
cursor = logMysql.cursor();
cursor.execute(addBackupLog,dataBackupLog);
logMysql.commit();
cursor.close();
except errorcode:
print(errorcode);