`
yizhilong28
  • 浏览: 233105 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

python遇到sql2005 varchar(4000) varchar(MAX)

阅读更多
要往数据库里面写入一个长文本字段,这个字段是svg文件中polyline元素的points属性。
设计时,数据库字段设置为varchar(4000)
使用python读取svg,将points属性写入数据库,一切ok
使用python根据查询数据库,重新生成svg时,读出来的points数据少了一大截,

刚学python,算了,不搞了
用Java写了一个,一切ok
回头看看,Java映射成的是varchar(MAX)

重新运行python,读出来的数据一切ok

晕死,原来是varchar(4000)的原因,
附:
python3连接mssql麻烦,改用python2.6

连接使用pymssql-1.0.2.win32-py2.6.exe


转点mssl的帮助,mark下
1.  What are the new data types or enhanced data types in SQL Server 2005??
    Microsoft SQL Server 2005 introduces a new data type, the XML data type.  The XML data type lets you store XML documents and fragments in a SQL Server database.  An XML fragment is an XML instance that is missing a single top-level element.  You can create columns and variables of XML type and store XML instanced in them.
    In addition to the new XML data type, Microsoft SQL Server 2005 has enhanced three of the existing SQL Server data types, namely the VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.  The VARCHAR(MAX) data type indicates that the maximum storage size for the VARCHAR data type is 2^31-1 bytes.  The NVARCHAR data types indicates that the maximum storage size fr the NVARCHAR data type is 2^31-1 bytes.  Lastly, the VARBINARY(MAX) data type indicates that the maximum storage size for the VARBINARY data type is 2^31-1 bytes.

2.  What’s the maximum length for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types?

    The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).  The storage size is the actual length of data entered + 2 bytes.  The data entered can be 0 characters in length.  Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

   The maximum storage size for NVARCHAR(MAX) is also 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).  The storage size, in bytes, is two times the number of characters entered + 2 bytes.  The data entered can be 0 characters in length.  Since each Unicode character in an NVARCHAR data type uses two bytes, the maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.

   The maximum storage size for VARBINARY(MAX) is the same as the maximum storage size for VARCHAR(MAX) and NVARCHAR(MAX), which is 2^31-1 (2,147,483,647 bytes or 2GB - 1 bytes).  The storage size is the actual length of the data entered + 2 bytes.  The data that is entered can be 0 bytes in length.


3.  Since you can use VARCHAR(MAX), can I define any length for a VARCHAR data type such as VARCHAR(10000)?

    When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000.  If the length is greater than 8000, you have to use the  MAX specifier as the length.  If a length greater than 8000 is specified, the following error will be encountered (assuming that the length specified is 10000):

    Server: Msg 131, Level 15, State 3, Line 1
    The size (10000) given to the type 'varchar' exceeds the maximum  allowed for any data type (8000).

4.  Will VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) replace TEXT, NTEXT and IMAGE data types?
   Yes, VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) will replace the TEXT, NTEXT and IMAGE data types, respectively.  TEXT, NTEXT and IMAGE data types will be removed in the future version of Microsoft SQL Server.  Avoid using these data types when using SQL Server 2005 and use VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) instead.

5. Can I declare a local variable of VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX)?

   Yes, you can declare local variables of VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.  This is unlike the data types that these enhanced data types are replacing because declaring local variables of TEXT, NTEXT and IMAGE data types is not allowed.  You will encounter the following error message when a local variable of TEXT, NTEXT or IMAGE data type is defined:

    Server: Msg 2739, Level 16, State 1, Line 7
    The text, ntext, and image data types are invalid for local variables.

6.  If there are VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types, are there also CHAR(MAX), NCHAR(MAX) and BINARY(MAX) data types?

    No, there is no CHAR(MAX), NCHAR(MAX) or BINARY(MAX).  The main reason why the MAX specifier is not included for these data types is because these data types are fixed-length data.  If the MAX specifier was included for these data types, it will be a big waste of disk space as each column will consume 2GB-1 bytes even if only a short string value is assigned to the column.

7.  How can I make sure that my VARCHAR column will only contain 10,000 characters?  Defining it as VARCHAR(10000) generates an error of "The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000)."

To create a column of VARCHAR data type with a maximum of 10,000 characters, you declare the column as VARCHAR(MAX) data type and simply add a CHECK constraint to the column by checking the length of the column and making sure it is less than or equal to 10,000 characters.  To illustrate, here's how it will look like:

CREATE TABLE [dbo].[VarChar10000] ( [VarChar10000] VARCHAR(MAX) )
GO

ALTER TABLE [dbo].[VarChar10000]
    ADD CONSTRAINT [MaxLength10000]
    CHECK (DATALENGTH([VarChar10000]) <= 10000)
GO
With the column defined as VARCHAR(MAX), it can accept a string longer than 8,000 characters.  But with the CHECK constraint, it will not allow a string with a length of more than 10,000 characters.  To verify that the column will not allow a string of more than 10,000 characters, try executing the following INSERT command, which will insert a string value of 11,000 characters:

     INSERT INTO [dbo].[VarChar10000] ( [VarChar10000] )
     VALUES (REPLICATE(CAST('A' AS VARCHAR(MAX)), 11000))

The following error will be shown when the INSERT statement above is executed:

Server: Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the CHECK constraint "MaxLength10000".
The conflict occurred in database, table "dbo.VarChar10000", column 'VarChar10000'.
The statement has been terminated.















  • dev.rar (20.3 KB)
  • 下载次数: 3
分享到:
评论

相关推荐

    sqlserver与myqsql区别

    在处理大数据方面,MySQL使用的是TEXT类型,而SQL Server使用的是varchar(max)类型。另外,在索引创建上,MySQL有关键字KEY,而SQL Server使用的是CREATE UNIQUE NONCLUSTERED INDEX。这也体现了两种数据库在索引...

    在Django框架中运行Python应用全攻略

    ### 在Django框架中运行Python应用全攻略 #### 一、前言 本文旨在全面介绍如何在Django框架中运行Python应用,特别是针对那些已经具备一定基础,希望深入了解Django框架内部机制及其应用配置的开发者。在此之前,...

    SQL大总结——转载经典——价值过亿

    11. **SQL与编程语言的交互**:在实际开发中,我们通常通过编程语言(如Java、Python等)来调用SQL,进行数据操作,了解如何在代码中执行SQL语句至关重要。 12. **数据库设计与规范化**:良好的数据库设计遵循范式...

    一次学透SQL视频教程视频课程下载整理.zip

    【SQL基础概念与语法】 ...总的来说,"一次学透SQL视频教程"涵盖了SQL从入门到进阶的全面内容,并可能与其他技术如Python编程、嵌入式开发、小程序开发等有所融合,旨在帮助你构建坚实的数据管理技能。

    Sql for cankao

    ### SQL for 参考 #### 重要知识点概览 1. **连接与断开 MySQL 服务器** - 如何使用 `mysql` 命令行工具连接 MySQL 服务器。 - 连接时所需的参数(主机名、用户名、密码)。 - 断开连接的方法。 2. **输入 SQL...

    MySQL安装及配置以及与python连接.docx

    cursor.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10));") ``` - **插入数据:** ```python sql = "insert into student values('2','Tom','3 year 2 class','9')...

    SQL21天自学通

    - SQL可以集成到各种编程语言中,如Java、Python等。 - 通过API或数据库驱动器进行交互。 #### 第二天:查询——SELECT语句的使用 - **目标**: - 学会如何构建基本的SELECT语句。 - 掌握从单个表中检索数据的...

    漫画SQL-mysql 必修课视频课程下载整理.zip

    【漫画SQL——mysql 必修课】是一门以轻松易懂的漫画形式讲解MySQL数据库基础的视频课程。针对初学者,这门课程旨在通过生动有趣的方式帮助学习者掌握SQL语言和MySQL数据库管理系统的基础知识,为日后的数据分析、...

    SQL精华和语言参考大全

    在描述中提到的API工具类可能是指一些库或框架,如Python的pandas或者Java的JDBC,它们提供了与SQL数据库交互的接口,使得在编程环境中执行SQL查询变得更加便捷。这些工具允许你执行CRUD操作(创建、读取、更新和...

    SQL优化面试专题及答案.pdf

    ### SQL优化面试专题及答案知识点梳理 #### 1. 自增主键ID问题 - **MyISAM表特性**:自增ID记录在数据文件中,即使重启数据库,自增ID也不会丢失,因此在上述情况下,插入的新记录ID为18。 - **InnoDB表特性**:...

    详细的sql调用webservice.rar

    DECLARE @responseText VARCHAR(MAX) EXEC sp_OAGetProperty @obj, 'responseText', @responseText OUT; -- 处理返回的XML数据 END EXEC sp_OADestroy @obj; ``` 2. **Oracle中的Web服务调用**:Oracle提供了...

    Python库 | duckdb-0.2.6.dev790-cp37-cp37m-win32.whl

    **Python库 DuckDB详解** DuckDB 是一个开源的关系型数据库管理系统(RDBMS),它设计为嵌入式,可以在单个进程中运行,适合在内存或磁盘上处理大量数据。DuckDB 以高性能、易用性和兼容性为核心特点,尤其适用于...

    SQL21自学通

    - 许多高级编程语言(如Java、Python等)支持通过连接器与数据库交互,执行SQL命令。 #### 二、SELECT语句的使用 - **一般的语法规则:** - SELECT语句用于从数据库中检索数据。 - 基本语法为:`SELECT column_...

    sql21自学通

    - SQL常被用于网页开发中,如PHP、Python等后端编程语言可以与数据库进行交互。 - 数据分析和数据科学领域也广泛使用SQL来处理大量数据集。 #### 二、SELECT语句详解 - **一般的语法规则:** - SELECT ...

    SQL进行数据分析:使用SQL进行数据分析并提取有用的见解

    在Jupyter Notebook环境中使用SQL,可以通过集成的库(如`pandasql`或`sqlalchemy`)将SQL语句与Python结合,方便地执行查询并把结果转换为DataFrame,便于进一步的数据处理和可视化。 在“SQL-for-Data-Analysis-...

Global site tag (gtag.js) - Google Analytics