1. 安装IronPython，到http://ironpython.codeplex.com/下载安装包
2. 创建项目
创建一个C#的控制台应用程序。
添加引用： 浏览到IronPython的安装目录中，添加对IronPython.dll，Microsoft.Scripting.dll 两个dll的引用。
3. 添加Python文件到当前的项目中
创建一个文本文件命名为：hello.py, 编辑如下
def welcome(name):
return "hello" + name
把该文件添加的当前的项目中。
4. 在C#中调用Python方法
首先添加两个引用：它们定义了Python和ScriptRuntime两个类型。
第一句代码创建了一个Python的运行环境，第二句则使用.net4.0的语法创建了一个动态的对象， OK，下面就可以用这个dynamic类型的对象去调用刚才在定义的welcome方法了。
注意：在运行前一定要把hello.py文件设为：Copy always.否则运行时会报找不到hello.py文件，enjoy it!
----------
前言
InronPython是一种在.NET和Mono上实现的Python语言，使用InronPython就可以在.NET环境中调用Python代码，非常方便。
本文主要介绍在C#中调用Python代码，并使用Python安装的第三方模块。
安装InronPython
要在.NET环境中使用Python，先要安装InronPython（当然也要安装Python），安装很简单，直接下载安装就行。在CodePlex就有下载，下载地址：
还可以把 Python Tools for Visual Studio 也安装了。
使用
添加引用库
在Visual Studio新建一个工程后，添加引用IronPython.dll和Microsoft.Scripting.dll（位于InronPython的安装目录下，如下图）。
C#代码内嵌Python
最简单的使用方式如下：
var engine = IronPython.Hosting.Python.CreateEngine();
engine.CreateScriptSourceFromString("print 'hello world!'").Execute();
从文件中加载Python代码
一般情况下我们还是要把Python代码单独写在文件中。在工程中新建一个Python文件，如 hello.py ，并设置其属性Copy to Output Directory的值为Copy if newer。在hello.py下编写如下代码：
def say_hello():
print "hello!"
def get_text():
return "text from hello.py"
def add(arg1, arg2):
return arg1 + arg2
C#代码如下：
var engine = IronPython.Hosting.Python.CreateEngine();
var scope = engine.CreateScope();
var source = engine.CreateScriptSourceFromFile("hello.py");
source.Execute(scope);
var say_hello = scope.GetVariable<Func<object>>("say_hello");
say_hello();
var get_text = scope.GetVariable<Func<object>>("get_text");
var text = get_text().ToString();
Console.WriteLine(text);
var add = scope.GetVariable<Func<object, object, object>>("add");
var result1 = add(1, 2);
Console.WriteLine(result1);
var result2 = add("hello ", "world");
Console.WriteLine(result2);
使用Python安装的第三模块
问题
到此为止，程序运行得很顺利。可是好景不长，最近用Python写了个程序要使用rsa加密，在Python中安装了rsa模块（下载地址： https://pypi.python.org/pypi/rsa/3.1.1 ）后，直接运行Python代码没问题，可是在C#代码调用时就报异常，异常信息如下：
An unhandled exception of type 'IronPython.Runtime.Exceptions.ImportException' occurred in Microsoft.Dynamic.dll
Additional information: No module named rsa
没有找到模块，经过一番google，说是要设置 sys.path ，如下：
import sys
sys.path.append(r"c:python27lib")
照做之后问题依旧。不过想一想，应该是sys.path还没设置对。
解决
先在python代码加上下面几行：
import sys
sys.path.append(r"c:python27lib")
print sys.path
运行查看输出，在对比Python环境下的 sys.path ，果然不一样，问题应该就出在 sys.path 上了。
在cmd下分别打开python和IronPython(在IronPython安装目录下的ipy64.exe或ipy.exe)，执行 import sys;print sys.path ，对比输出：
python:
['', 'C:\Python27\lib\site-packages\setuptools-12.0.3-py2.7.egg', 'C:\Pytho
n27\lib\site-packages\rsa-3.1.1-py2.7.egg', 'C:\Python27\lib\site-packages
\pyasn1-0.1.7-py2.7.egg', 'C:\Windows\SYSTEM32\python27.zip', 'C:\Python27
DLLs', 'C:\Python27\lib', 'C:\Python27\lib\plat-win', 'C:\Python27\lib\
lib-tk', 'C:\Python27', 'C:\Python27\lib\site-packages']
IronPython:
['.', 'C:\Program Files (x86)\IronPython 2.7\Lib', 'C:\Program Files (x86)\
IronPython 2.7\DLLs', 'C:\Program Files (x86)\IronPython 2.7', 'C:\Program F
iles (x86)\IronPython 2.7\lib\site-packages']
分别在两个环境在执行 import rsa ，Python环境正常，IronPython环境下报ImportError: No module named rsa异常。在IronPython环境下执行如下操作：
sys.path.append('C:\Python27\lib\site-packages\setuptools-12.0.3-py2.7.egg')
sys.path.append('C:\Python27\lib\site-packages\rsa-3.1.1-py2.7.egg')
再次 import rsa ，不报异常了。
对应的python代码文件加上如下几行：
import sys
sys.path.append('C:\Python27\lib')
sys.path.append('C:\Python27\lib\site-packages\setuptools-12.0.3-py2.7.egg')
sys.path.append('C:\Python27\lib\site-packages\rsa-3.1.1-py2.7.egg')
import rsa
再次运行，Ok！
------------------------
给客户安装程序时除了安装.net framework还要安装IronPython，是不是觉得很麻烦？
上面这一切都弱爆了，下面我来介绍一种不安装IronPython只需要引入几个IronPython的dll就可以在c#中执行python脚本的方法。
1：引入IronPython中的几个dll
2：进入IronPython的Lib文件夹，把Lib中的内容打包成zip，名字任意既可。打包好后放到c#项目下
我把它放到了和py文件同一个目录中
3：很关键的一步，程序初始化时执行下段代码
ScriptEngine engine = Python.CreateEngine();
ScriptScope scope = engine.CreateScope();
ScriptSource source = engine.CreateScriptSourceFromString(
@"import sys" "
"
@"sys.path.append("".scriptspythonlib.zip"")" "
"
@"sys.path.append("".scripts"")" "
"
);
source.Execute(scope);将zip文件加入python库路径。这样能保证py脚本可以正确搜索到python库的位置。
4：尽情享用脚本语言带来的便利吧。为其他人安装程序时也不用安装讨厌的IronPython环境了。
-------------
C#调用python文件执行
我的电脑环境是使用.net framework4.5.1,如果在调试过程中调不通请注意
我用的是Visual studion 2017,python组件下载地址：http://ironpython.codeplex.com/releases/view/
下载的版本是2.7,下载安装完之后记得引入安装路径下的以下三个dll
(1)首先先说一个简单的功能,在c#代码中执行python字符串,内容如下:
(2)c#调用python文件:
在当前目录下新建一个后缀名为py的文件,文件名为AmoutDisc.py内容如下
dicAmt = 0.05 retAmt = amt if amt>25: retAmt = amt-(amt*dicAmt)
该文件定义了一个python文件,然后在c#代码中调用该python文件,给该文件传参,具体应该说给参数amt传值,最后在代码中获取python文件中的retAmt的变量
值,具体代码如下:
ScriptRuntime scriptRuntime =Python.CreateRuntime();
ScriptEngine pythEng = scriptRuntime.GetEngine("Python");
ScriptSource scriptSource = pythEng.CreateScriptSourceFromFile("AmoutDisc.py");
ScriptScope scope = pythEng.CreateScope();
//scope.SetVariable("prodCount", Convert.ToInt32("34343"));
scope.SetVariable("amt", Convert.ToDecimal("434"));
scriptSource.Execute(scope);
dynamic a = scope.GetVariable("retAmt");
结果如下:
调用成功.
---------
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
ShellExecute 0,"Open","Python.exe","C:Mypython.py","",1
End Sub
以上请参考!
-----------
问题的描述：
鉴于Python强大的网络功能和丰富的开源组件和C#开发Windows Form程序时优秀的框架，使用Python和C#混合编程可以有效的结合二者的长处，快速开发产品。
然而在这两者之间如何进行消息/数据的传递便成为一个首要的问题。
解决思路：
想在这两者之间方便进行对象转换Json是个首选的技术（关于Json的介绍就不复述了，可以参考文献[1]）。既然选定了使用Json作为消息传递的格式，我们可以有两种方案处理这个问题：
一、通过中间文件传递Json字符串
在Python中将需要传递到C#的对象组装成列表，转换为Json字符串，将该字符串写入文件，然后再在C#端将该文件读出来，反序列化为对象列表即可。
话不多说，直接上代码：
Python端代码：
注：这里只贴了核心代码。至于如何将自定义Python转换为Json字符串可以参考文献[2]。
C#端代码：
二、通过返回值传递Json字符串
在Python端将对象列表转换成的Json字符串直接返回，在C#端直接捕获该返回值，然后反序列化为对象列表，代码如下：
Python端
C#端
这种方式省略了对中间文件的读写操作，较少了程序运行的时空开销。
参考文献：
【1】介绍Json http://www.json.org/json-zh.html
【2】使用Json作为Python和C#混合编程时对象转换的中间文件 http://www.cnblogs.com/chaosimple/p/4035693.html
【3】Best way to return a value from a Python script http://stackoverflow.com/questions/18231415/best-way-to-return-a-value-from-a-python-script
-----------
ython 连接操作 各类数据库
摘要：
用Python写脚本也有一段时间了，经常操作数据库（MySQL），现在就整理下对各类数据库的操作，如后面有新的参数会补进来，慢慢完善。
一，python 操作 MySQL:详情见：这里
mac 安装：http://sourceforge.net/projects/mysql-python/?source=dlp 再 sudo python setup.py build
【apt-get install python-mysqldb】
#!/bin/env python
# -*- encoding: utf-8 -*-
#-------------------------------------------------------------------------------
# Purpose: example for python_to_mysql
# Author: zhoujy
# Created: 2013-06-14
# update: 2013-06-14
#-------------------------------------------------------------------------------
import MySQLdb
import os
#建立和数据库系统的连接，格式
#conn = MySQLdb.connect(host='localhost',user='root',passwd='123456',db='test',port=3306,charset='utf8')
#指定配置文件，确定目录,或则写绝对路径
cwd = os.path.realpath(os.path.dirname(__file__))
db_conf = os.path.join(cwd, 'db.conf')
conn = MySQLdb.connect(read_default_file=db_conf,host='localhost',db='test',port=3306,charset='utf8')
#要执行的sql语句
query = 'select id from t1'
#获取操作游标
cursor = conn.cursor()
#执行SQL
cursor.execute(query)
#获取一条记录,每条记录做为一个元组返回,返回3，游标指到第2条记录。
result1 = cursor.fetchone()
for i in result1:
print i
#返回影响的行数
print cursor.rowcount
#获取指定数量记录,每条记录做为一个元组返回,返回1，2，游标从第2条记录开始，游标指到第4条记录。
result2 = cursor.fetchmany(2)
for i in result2:
for ii in i:
print ii
#获取所有记录,每条记录做为一个元组返回,返回3，4，7，6,游标从第4条记录开始到最后。
result3 = cursor.fetchall()
for i in result3:
for ii in i:
print ii
#获取所有记录,每条记录做为一个元组返回,返回3，4，7，6,游标从第1条记录开始
#重置游标位置，0为偏移量，mode＝absolute | relative,默认为relative
cursor.scroll(0,mode='absolute')
result3 = cursor.fetchall()
for i in result3:
for ii in i:
print ii
#以下2种方法都可以把数据插入数据库：
#(one)
for i in range (10,20):
query2 = 'insert into t1 values("%d",now())' %i
cursor.execute(query2)
#提交
conn.rollback()
#(two)
rows = []
for i in range (10,20):
rows.append(i)
query2 = 'insert into t1 values("%s",now())'
#executemany 2个参数,第2个参数是变量。
cursor.executemany(query2,rows)
#提交
conn.commit()
#选择数据库
query3 = 'select id from dba_hospital'
#重新选择数据库
conn.select_db('chushihua')
cursor.execute(query3)
result4 = cursor.fetchall()
for i in result4:
for ii in i:
print ii
#不定义query，直接执行：
cursor.execute("set session binlog_format='mixed'")
#关闭游标，释放资源
cursor.close()
'''
+------+---------------------+
| id | modifyT |
+------+---------------------+
| 3 | 2010-01-01 00:00:00 |
| 1 | 2010-01-01 00:00:00 |
| 2 | 2010-01-01 00:00:00 |
| 3 | 2010-01-01 00:00:00 |
| 4 | 2013-06-04 17:04:54 |
| 7 | 2013-06-04 17:05:36 |
| 6 | 2013-06-04 17:05:17 |
+------+---------------------+
'''
注意：在脚本中，密码写在脚本里面很容易暴露，这样可以用一个配置文件的方式来存密码，如db.conf：
[client] user=root password=123456
二，python 操作 MongoDB:详情见这里和这里以及这里
#!/bin/env python
# -*- encoding: utf-8 -*-
#-------------------------------------------------------------------------------
# Purpose: example for python_to_mongodb
# Author: zhoujy
# Created: 2013-06-14
# update: 2013-06-14
#-------------------------------------------------------------------------------
import pymongo
import os
#建立和数据库系统的连接,创建Connection时，指定host及port参数
conn = pymongo.Connection(host='127.0.0.1',port=27017)
#admin 数据库有帐号，连接-认证-切换库
db_auth = conn.admin
db_auth.authenticate('sa','sa')
#连接数据库
db = conn.abc
#连接表
collection = db.stu
#查看全部表名称
db.collection_names()
#print db.collection_names()
#访问表的数据，指定列
item = collection.find({},{"sname":1,"course":1,"_id":0})
for rows in item:
print rows.values()
#访问表的一行数据
print collection.find_one()
#得到所有的列
for rows in collection.find_one():
print rows
#插入
collection.insert({"sno":100,"sname":"jl","course":{"D":80,"S":85}})
#或
u = dict(sno=102,sname='zjjj',course={"D":80,"S":85})
collection.insert(u)
#得到行数
print collection.find().count()
print collection.find({"sno":100})
#排序，按照某一列的值。pymongo.DESCENDING:倒序；pymongo.ASCENDING:升序。按照sno倒序
item = collection.find().sort('sno',pymongo.DESCENDING)
for rows in item:
print rows.values()
#多列排序
item = collection.find().sort([('sno',pymongo.DESCENDING),('A',pymongo.ASCENDING)])
#更新，第一个参数是条件，第二个参数是更新操作，$set,%inc,$push,$ne,$addToSet,$rename 等
collection.update({"sno":100},{"$set":{"sno":101}})
#更新多行和多列
collection.update({"sno":102},{"$set":{"sno":105,"sname":"SSSS"}},multi=True)
#删除，第一个参数是条件，第二个参数是删除操作。
collection.remove({"sno":101})
'''
sno:学号；sname：姓名；course：科目
db.stu.insert({"sno":1,"sname":"张三","course":{"A":95,"B":90,"C":65,"D":74,"E":100}})
db.stu.insert({"sno":2,"sname":"李四","course":{"A":90,"B":85,"X":75,"Y":64,"Z":95}})
db.stu.insert({"sno":3,"sname":"赵五","course":{"A":70,"B":56,"F":85,"G":84,"H":80}})
db.stu.insert({"sno":4,"sname":"zhoujy","course":{"A":64,"B":60,"C":95,"T":94,"Y":85}})
db.stu.insert({"sno":5,"sname":"abc","course":{"A":87,"B":70,"Z":56,"G":54,"H":75}})
db.stu.insert({"sno":6,"sname":"杨六","course":{"A":65,"U":80,"C":78,"R":75,"N":90}})
db.stu.insert({"sno":7,"sname":"陈二","course":{"A":95,"M":68,"N":84,"S":79,"K":89}})
db.stu.insert({"sno":8,"sname":"zhoujj","course":{"P":90,"B":77,"J":85,"K":68,"L":80}})
db.stu.insert({"sno":9,"sname":"ccc","course":{"Q":85,"B":86,"C":90,"V":87,"U":85}})
'''
计算Mongodb文档中各集合的数目：
import pymongo
conn = pymongo.Connection(host='127.0.0.1',port=27017)
db = conn.abc #abc文档
for tb_name in db.collection_names(): #循环出各集合名
Count = db[tb_name].count() #计算各集合的数量
if Count > 2: #过滤条件
print tb_name + ':' + str(Count)
'''
conn = pymongo.Connection(host='127.0.0.1',port=27017)
db = conn.abc
for tb_name in db.collection_names():
print tb_name + ':'
exec('print ' + 'db.'+tb_name+'.count()') #变量当集合的处理方式
OR
conn = pymongo.Connection(host='127.0.0.1',port=27017)
db = conn.abc
for tb_name in db.collection_names():
mon_dic=db.command("collStats", tb_name) #以字典形式返回
print mon_dic.get('ns'),mon_dic.get('count')
'''
updatetime: 2015-06-30
MongoDB升级到了3.0之后，用python的连接会出错：
pymongo.errors.OperationFailure: command SON([('authenticate', 1), ('user', u'dba'), ('nonce', u'8c7842b068e14d3'), ('key', u'584ec63f1cdfd8525ce33d99cd269c2c')]) failed: auth failed
表示认证失败，说明MongoDB升级之后，对用用户的加密方式改变了。那就升级pymongo。
zhoujy@zhoujy:~$ sudo pip install pymongo --upgrade [sudo] password for zhoujy: ... ... Successfully installed pymongo Cleaning up...
升级成功，要是没有安装pip，看这里：http://www.saltycrane.com/blog/2010/02/how-install-pip-ubuntu/
ubuntu10.10之后：
$ sudo apt-get install python-pip python-dev build-essential $ sudo pip install --upgrade pip $ sudo pip install --upgrade virtualenv
ubuntu10.10之前的老版本：
$ sudo apt-get install python-setuptools python-dev build-essential $ sudo easy_install pip $ sudo pip install --upgrade virtualenv
升级成功之后，继续执行python脚本，还是出错：
AttributeError: 'module' object has no attribute 'Connection'
表示没有 Connection ，pymongo升级完之后不支持了，看手册，发现用MongoClient 来替换了Connection。修改脚本：
conn = pymongo.Connection(host='127.0.0.1',port=27017) 改成 conn = pymongo.MongoClient(host='127.0.0.1',port=27017)
最后执行python，正常。
三，python 操作 Redis:详情见这里 和 这里/这里；集群连接
#!/bin/env python
# -*- encoding: utf-8 -*-
#-------------------------------------------------------------------------------
# Purpose: example for python_to_mongodb
# Author: zhoujy
# Created: 2013-06-14
# update: 2013-06-14
#-------------------------------------------------------------------------------
import redis
f = open('aa.txt')
while True:
line = f.readline().strip().split(' # ')
if line == ['']:
break
UserName,Pwd,Email = line
# print name.strip(),pwd.strip(),email.strip()
rc = redis.StrictRedis(host='127.0.0.1',port=6379,db=15)
rc.hset('Name:' + UserName,'Email',Email)
rc.hset('Name:' + UserName,'Password',Pwd)
f.close()
alluser = rc.keys('*')
#print alluser
print "===================================读出存进去的数据==================================="
for user in alluser:
print ' # '.join((user.split(':')[1],rc.hget(user,'Password'),rc.hget(user,'Email')))
一个pipeline的请看：这里
四，python 操作 memcache:详情见 这里 和 这里
import memcache
mc = memcache.Client(['127.0.0.1:11211'],debug=1)
#!/usr/bin/env python
#coding=utf-8
import MySQLdb
import memcache
import sys
import time
def get_data(mysql_conn):
# nn = raw_input("press string name:")
mc = memcache.Client(['127.0.0.1:11211'],debug=1)
t1 =time.time()
value = mc.get('zhoujinyia')
if value == None:
t1 = time.time()
print t1
query = "select company,email,sex,address from uc_user_offline where realName = 'zhoujinyia'"
cursor= mysql_conn.cursor()
cursor.execute(query)
item = cursor.fetchone()
t2 = time.time()
print t2
t = round(t2-t1)
print "from mysql cost %s sec" %t
print item
mc.set('zhoujinyia',item,60)
else :
t2 = time.time()
t=round(t2-t1)
print "from memcache cost %s sec" %t
print value
if __name__ =='__main__':
mysql_conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123456',db='member',port=3306,charset='utf8')
get_data(mysql_conn)
上面介绍了一些python连接数据库，红色部分是连接操作的关键部分，大部分的操作没有列出来，具体的请见各官网。
~~~~~~~~~~~~~~~ 万物之中,希望至美 ~~~~~~~~~~~~~~~
-------------
-
本文主要介绍Python连接各种数据库的方法及简单使用
包括关系数据库：sqlite,mysql,mssql
非关系数据库：MongoDB,Redis代码写的比较清楚，直接上代码
1.连接sqlite
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283<code class="hljs python"># coding=utf-8import sqlite3import tracebacktry:# 如果表不存在,就创建withsqlite3.connect('test.db')asconn:print("Opened database successfully")# 删除表conn.execute("DROP TABLE IF EXISTS COMPANY")# 创建表sql ="""CREATE TABLE IF NOT EXISTS COMPANY(ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);"""conn.execute(sql)print("create table successfully")# 添加数据conn.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES (?, ?, ?, ? )",[('Paul', 32,'California', 20000.00),('Allen', 25,'Texas', 15000.00),('Teddy', 23,'Norway', 20000.00),('Mark', 25,'Rich-Mond ', 65000.00),('David', 27,'Texas', 85000.00),('Kim', 22,'South-Hall', 45000.00),('James', 24,'Houston', 10000.00)])# conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)# VALUES ( 'Paul', 32, 'California', 20000.00 )")## conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)# VALUES ('Allen', 25, 'Texas', 15000.00 )")## conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)# VALUES ('Teddy', 23, 'Norway', 20000.00 )")## conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)# VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 )")## conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)# VALUES ( 'David', 27, 'Texas', 85000.00 )");## conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)# VALUES ( 'Kim', 22, 'South-Hall', 45000.00 )")## conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)# VALUES ( 'James', 24, 'Houston', 10000.00 )")# 提交,否则重新运行程序时,表中无数据conn.commit()print("insert successfully")# 查询表sql ="""select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY"""result = conn.execute(sql)forrowinresult:print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("id", row[0])) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row[1]))print("%-10s %s"% ("age", row[2]))print("%-10s %s"% ("address", row[3]))print("%-10s %.2f"% ("salary", row[4]))#or# print('{:10s} {:.2f}'.format("salary", row[4]))exceptsqlite3.Errorase:print("sqlite3 Error:", e)traceback.print_exc()</code>2.连接mysql2.1使用mysqldb库中的_mysql1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465<code class="hljs python"><code class="hljs python">#! /usr/bin/env python2.7# coding=utf-8# Createdbyxiaosanyuat16/5/30# mysqldb 只支持python2.7import MySQLdbfromcontextlib import closingimport tracebacktry:# 获取一个数据库连接withclosing(MySQLdb.connect(host='localhost',user='root', passwd='root', db='test', port=3306,charset='utf8'))asconn:print("connect database successfully")withclosing(conn.cursor())ascur:# 删除表cur.execute("DROP TABLE IF EXISTS COMPANY")# 创建表sql ="""CREATE TABLE IF NOT EXISTS COMPANY(ID INTEGER PRIMARY KEY NOT NULL auto_increment,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);"""cur.execute(sql)print("create table successfully")# 添加数据# 在一个conn.execute里面里面执行多个sql语句是非法的cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",[('Paul', 32,'California', 20000.00),('Allen', 25,'Texas', 15000.00),('Teddy', 23,'Norway', 20000.00),('Mark', 25,'Rich-Mond ', 65000.00),('David', 27,'Texas', 85000.00),('Kim', 22,'South-Hall', 45000.00),('James', 24,'Houston', 10000.00)])# 提交,否则重新运行程序时,表中无数据conn.commit()print("insert successfully")# 查询表sql ="""select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY"""cur.execute(sql)forrowincur.fetchall():print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("id", row[0])) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row[1]))print("%-10s %s"% ("age", row[2]))print("%-10s %s"% ("address", row[3]))print("%-10s %s"% ("salary", row[4]))exceptMySQLdb.Errorase:print("Mysql Error:", e)traceback.print_exc() # 打印错误栈信息</code></code>2.2 使用MySQLdb1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465<code class="hljs python"><code class="hljs python"><code class="hljs python">#! /usr/bin/env python2.7# coding=utf-8# Createdbyxiaosanyuat16/5/30# mysqldb 只支持python2.7import MySQLdbfromcontextlib import closingimport tracebacktry:# 获取一个数据库连接withclosing(MySQLdb.connect(host='localhost',user='root', passwd='root', db='test', port=3306,charset='utf8'))asconn:print("connect database successfully")withclosing(conn.cursor())ascur:# 删除表cur.execute("DROP TABLE IF EXISTS COMPANY")# 创建表sql ="""CREATE TABLE IF NOT EXISTS COMPANY(ID INTEGER PRIMARY KEY NOT NULL auto_increment,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);"""cur.execute(sql)print("create table successfully")# 添加数据# 在一个conn.execute里面里面执行多个sql语句是非法的cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",[('Paul', 32,'California', 20000.00),('Allen', 25,'Texas', 15000.00),('Teddy', 23,'Norway', 20000.00),('Mark', 25,'Rich-Mond ', 65000.00),('David', 27,'Texas', 85000.00),('Kim', 22,'South-Hall', 45000.00),('James', 24,'Houston', 10000.00)])# 提交,否则重新运行程序时,表中无数据conn.commit()print("insert successfully")# 查询表sql ="""select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY"""cur.execute(sql)forrowincur.fetchall():print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("id", row[0])) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row[1]))print("%-10s %s"% ("age", row[2]))print("%-10s %s"% ("address", row[3]))print("%-10s %s"% ("salary", row[4]))exceptMySQLdb.Errorase:print("Mysql Error:", e)traceback.print_exc() # 打印错误栈信息</code></code></code>2.3使用pymysql2.1和2.2节使用MySQLdb，不支持Python3.x
pymysql对Python2.x和Python3.x的支持都比较好123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566<code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"># Createdbyxiaosanyuat16/5/30# coding=utf-8import pymysqlfromcontextlib import closingimport tracebacktry:# 获取一个数据库连接,with关键字 表示退出时,conn自动关闭#with嵌套上一层的with要使用closing()withclosing(pymysql.connect(host='localhost',user='root', passwd='root', db='test', port=3306,charset='utf8'))asconn:print("connect database successfully")# 获取游标,with关键字 表示退出时,cur自动关闭withconn.cursor()ascur:# 删除表cur.execute("DROP TABLE IF EXISTS COMPANY")# 创建表sql ="""CREATE TABLE IF NOT EXISTS COMPANY(ID INTEGER PRIMARY KEY NOT NULL auto_increment,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);"""cur.execute(sql)print("create table successfully")# 添加数据# 在一个conn.execute里面里面执行多个sql语句是非法的cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",[('Paul', 32,'California', 20000.00),('Allen', 25,'Texas', 15000.00),('Teddy', 23,'Norway', 20000.00),('Mark', 25,'Rich-Mond ', 65000.00),('David', 27,'Texas', 85000.00),('Kim', 22,'South-Hall', 45000.00),('James', 24,'Houston', 10000.00)])# 提交,否则重新运行程序时,表中无数据conn.commit()print("insert successfully")# 查询表sql ="""select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY"""cur.execute(sql)forrowincur.fetchall():print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("id", row[0])) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row[1]))print("%-10s %s"% ("age", row[2]))print("%-10s %s"% ("address", row[3]))print("%-10s %s"% ("salary", row[4]))exceptpymysql.Errorase:print("Mysql Error:", e)traceback.print_exc()</code></code></code></code>3.连接mssql123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566<code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"># Createdbyxiaosanyuat16/5/30import pymssqlfromcontextlib import closingtry:# 先要保证数据库中有test数据库# 获取一个数据库连接,with关键字 表示退出时,conn自动关闭#with嵌套上一层的with要使用closing()withclosing(pymssql.connect(host='192.168.100.114',user='sa',password='sa12345',database='test', port=1433,charset='utf8'))asconn:print("connect database successfully")# 获取游标,with关键字 表示退出时,cur自动关闭withconn.cursor()ascur:# 删除表cur.execute('''if exists (select 1 from sys.objects where name='COMPANY' and type='U') drop table COMPANY''')# 创建表sql ="""CREATE TABLE COMPANY(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);"""cur.execute(sql)print("create table successfully")# 添加数据# 在一个conn.execute里面里面执行多个sql语句是非法的cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",[('Paul', 32,'California', 20000.00),('Allen', 25,'Texas', 15000.00),('Teddy', 23,'Norway', 20000.00),('Mark', 25,'Rich-Mond', 65000.00),('David', 27,'Texas', 85000.00),('Kim', 22,'South-Hall', 45000.00),('James', 24,'Houston', 10000.00)])# 提交,否则重新运行程序时,表中无数据conn.commit()print("insert successfully")# 查询表sql ="""select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY"""cur.execute(sql)forrowincur.fetchall():print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("id", row[0])) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row[1]))print("%-10s %s"% ("age", row[2]))print("%-10s %s"% ("address", row[3]))print("%-10s %s"% ("salary", row[4]))exceptpymssql.Errorase:print("mssql Error:", e)# traceback.print_exc()</code></code></code></code></code>4.连接MongoDB12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182<code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs perl"># Createdbyxiaosanyuat16/5/30import pymongofrompymongo.mongo_client import MongoClientimport pymongo.errorsimport tracebacktry:# 连接到 mongodb 服务mongoClient = MongoClient('localhost', 27017)# 连接到数据库mongoDatabase = mongoClient.testprint("connect database successfully")# 获取集合mongoCollection = mongoDatabase.COMPANY# 移除所有数据mongoCollection.remove()# 添加数据mongoCollection.insert_many([{"Name":"Paul","Age":"32","Address":"California","Salary":"20000.00"},{"Name":"Allen","Age":"25","Address":"Texas","Salary":"15000.00"},{"Name":"Teddy","Age":"23","Address":"Norway","Salary":"20000.00"},{"Name":"Mark","Age":"25","Address":"Rich-Mond","Salary":"65000.00"},{"Name":"David","Age":"27","Address":"Texas","Salary":"85000.00"},{"Name":"Kim","Age":"22","Address":"South-Hall","Salary":"45000.00"},{"Name":"James","Age":"24","Address":"Houston","Salary":"10000.00"}, ])#获取集合中的值forrowinmongoCollection.find():print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("_id", row['_id'])) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row['Name']))print("%-10s %s"% ("age", row['Age']))print("%-10s %s"% ("address", row['Address']))print("%-10s %s"% ("salary", row['Salary']))print(' ')# 使id自增mongoCollection.remove()# 创建计数表mongoDatabase.counters.save({"_id":"people_id","sequence_value": 0})# 创建存储过程mongoDatabase.system_js.getSequenceValue ='''function getSequenceValue(sequenceName){var sequenceDocument = db.counters.findAndModify({query: {_id: sequenceName},update: {$inc:{sequence_value: 1}},new:true});return sequenceDocument.sequence_value;}'''mongoCollection.insert_many([{"_id": mongoDatabase.eval("getSequenceValue('people_id')"),"Name":"Paul","Age":"32","Address":"California","Salary":"20000.00"},{"_id": mongoDatabase.eval("getSequenceValue('people_id')"),"Name":"Allen","Age":"25","Address":"Texas","Salary":"15000.00"},{"_id": mongoDatabase.eval("getSequenceValue('people_id')"),"Name":"Teddy","Age":"23","Address":"Norway","Salary":"20000.00"},{"_id": mongoDatabase.eval("getSequenceValue('people_id')"),"Name":"Mark","Age":"25","Address":"Rich-Mond","Salary":"65000.00"},{"_id": mongoDatabase.eval("getSequenceValue('people_id')"),"Name":"David","Age":"27","Address":"Texas","Salary":"85000.00"},{"_id": mongoDatabase.eval("getSequenceValue('people_id')"),"Name":"Kim","Age":"22","Address":"South-Hall","Salary":"45000.00"},{"_id": mongoDatabase.eval("getSequenceValue('people_id')"),"Name":"James","Age":"24","Address":"Houston","Salary":"10000.00"}, ])forrowinmongoCollection.find():print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("_id",int(row['_id']))) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row['Name']))print("%-10s %s"% ("age", row['Age']))print("%-10s %s"% ("address", row['Address']))print("%-10s %s"% ("salary", row['Salary']))exceptpymongo.errors.PyMongoErrorase:print("mongo Error:", e)traceback.print_exc()</code></code></code></code></code></code>5.连接Redis5.1使用redis1234567891011121314151617181920212223242526272829303132333435363738394041424344<code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs perl"><code class="hljs perl"># coding=utf-8# Createdbyxiaosanyuat16/5/31import redisr = redis.Redis(host='localhost', port=6379, db=0,password="12345")print("connect", r.ping())# 看信息info = r.info()#or查看部分信息# info = r.info("Server")# 输出信息items = info.items()fori, (key, value)inzip(range(len(items)), items):print("item %s----%s:%s"% (i,key, value))# 删除键和对应的值r.delete("company")# 可以一次性push一条或多条数据r.rpush("company", {"id": 1,"Name":"Paul","Age":"32","Address":"California","Salary":"20000.00"},{"id": 2,"Name":"Allen","Age":"25","Address":"Texas","Salary":"15000.00"},{"id": 3,"Name":"Teddy","Age":"23","Address":"Norway","Salary":"20000.00"})r.rpush("company", {"id": 4,"Name":"Mark","Age":"25","Address":"Rich-Mond","Salary":"65000.00"})r.rpush("company", {"id": 5,"Name":"David","Age":"27","Address":"Texas","Salary":"85000.00"})r.rpush("company", {"id": 6,"Name":"Kim","Age":"22","Address":"South-Hall","Salary":"45000.00"})r.rpush("company", {"id": 7,"Name":"James","Age":"24","Address":"Houston","Salary":"10000.00"})# eval用来将dict格式的字符串转换成dictforrowinmap(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))):print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("_id", row['id'])) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row['Name']))print("%-10s %s"% ("age", row['Age']))print("%-10s %s"% ("address", row['Address']))print("%-10s %s"% ("salary", row['Salary']))# 关闭当前连接r.shutdown()</code></code></code></code></code></code></code>5.2使用pyredis12345678910111213141516171819202122232425262728293031323334353637383940<code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs python"><code class="hljs perl"><code class="hljs perl"><code class="hljs python"># Createdbyxiaosanyuat16/5/30import pyredisr = pyredis.Client(host='localhost', port=6379,database=0,password="12345")print("connect", r.ping().decode("utf-8"))# 看信息# info = r.execute("info").decode()#or查看部分信息info = r.execute("info","Server").decode()# 输出信息print(info)# 删除键和对应的值r.delete("company")# 可以一次性push一条或多条数据r.rpush("company",'''{"id": 1, "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}''','''{"id": 2, "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}''','''{"id": 3, "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}''')r.rpush("company",'''{"id": 4, "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}''')r.rpush("company",'''{"id": 5, "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}''')r.rpush("company",'''{"id": 6, "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}''')r.rpush("company",'''{"id": 7, "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}''')# eval用来将dict格式的字符串转换成dictforrowinmap(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))):print("-"* 50) # 输出50个-,作为分界线print("%-10s %s"% ("_id", row['id'])) # 字段名固定10位宽度,并且左对齐print("%-10s %s"% ("name", row['Name']))print("%-10s %s"% ("age", row['Age']))print("%-10s %s"% ("address", row['Address']))print("%-10s %s"% ("salary", row['Salary']))# 关闭当前连接r.close()</code></code></code></code></code></code></code></code>