简介:
微软azure平台推出sqldatabase paas服务。现在使用python语言连接azure数据库的小demo。
准备环境:
1,azure sqldatebase数据库创建,参考http://www.windowsazure.cn/starter-guide/
第三课,创建云端的数据库,
2,创建demo表,我是使用管理工具创建,sql语句代码为:
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[demo1]')
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[demo1](
id [int] IDENTITY(1,1) NOT NULL,
name varchar(30) not null
CONSTRAINT [PK_demo] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
END;
GO
需要注意的事项为:sqldatabase必须有一列为聚合键,如果没有,创建表会失败,临时表没有该限制。
3,1台azure linux虚拟机,我使用的ubuntu14.04 LTS
4,1个csv文件,只包含1列中文
开始动手
1,安装包python,pyodbc,unixodbc,freetds-bin
2,开始配置,
(1)配置freetds.conf,常见位置/etc/freetds,/usr/local/etc/
# server specific section
[global]
# TDS protocol version
tds version = 7.2
client charset = UTF-8
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
; timeout = 10
; connect timeout = 10
# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512
[azure]
host = hostname.database.chinacloudapi.cn
port = 1433
tds version = 7.2
其中,tds version选择了7.2,添加了client charset,这是为了防止中文乱码
(2)配置odbcinst.ini位置在/etc
[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 1
(3)配置odbc.ini位置在/etc
[azure_odbc]
Driver = FreeTDS
Servername = azure
Database = azureea
其中azure_odbc为odbc的dsn,Servername为freetds配置节点,Driver为odbcinst的配置节点,Database为azure数据库名称
3,开始调试,调试的python代码为:
#coding:utf-8
#!/usr/bin/env python
import pyodbc
import csv
from datetime import datetime
conn = pyodbc.connect('DSN=azure_odbc;UID=username@host;PWD=xxxxx;CHARSET=utf8;')
cursor = conn.cursor()
csvfile=file('name.csv','rb')
reader=csv.reader(csvfile)
for line in reader:
print line[0]
name=line[0]
cursor.execute("insert into demo1(name) values (?);",name)
conn.commit()
conn.close()
csvfile.close()
到此,一个采集数据的小案例就完成了。