Thread: VARCHAR versus TEXT when using psychopg2, sqlalchemy and redshift
I'm using SQLAlchemy backed by psychopg2 to interact with my data inside of AWS Redshift. I'm trying to insert data and am getting a perplexing error with respect to text vs varchar data. Here is a source snipit:
conn = get_db_connection()
keys=["a","b"]
values=["aa","bb"]
tbl_qualtrics_dim = Table('qualtrics_dim', metadata,
Column('k', String(1024), primary_key=True),
Column('v', String(1024), nullable=False)
)
metadata.create_all(conn, checkfirst=True)
ins = tbl_qualtrics_dim.insert().values(k=keys, v=values)
conn.execute(ins)
This creates the table just fine as being two columns of varchar(1024). AWS doesn't support clob types. The last line throws an error though when trying to insert data:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "k" is of type character varying but expression is of type text[]
HINT: You will need to rewrite or cast the expression.
[SQL: 'INSERT INTO qualtrics_dim (k, v) VALUES (%(k)s, %(v)s)'] [parameters: {'v': ['aa', 'bb'], 'k': ['a', 'b']}]
A cast as I understand it doesn't work, e.g. I change this line:
ins = tbl_qualtrics_dim.insert().values(k=cast(keys, String(1024)), v=values)
And I get a casting error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot cast type text[] to character varying
[SQL: 'INSERT INTO qualtrics_dim (k, v) VALUES (CAST(%(param_1)s AS VARCHAR(1024)), %(v)s)'] [parameters: {'v': ['aa', 'bb'], 'param_1': ['a', 'b']}]
Any hints on what might be going wrong here?
Regards,
Chris
On Aug 30, 2015, at 11:59 AM, Christopher Brooks <brooksch@umich.edu> wrote:
> [SQL: 'INSERT INTO qualtrics_dim (k, v) VALUES (%(k)s, %(v)s)'] [parameters: {'v': ['aa', 'bb'], 'k': ['a', 'b']}]
The values you've written there are two arrays of TEXT values, but the columns aren't declared as TEXT or VARCHAR
arrays,but as single VARCHAR values. Are you trying to insert two rows of the form 'aa', 'a' and 'bb', 'b'?
--
-- Christophe Pettus
xof@thebuild.com
Yes, this is what I was aiming to do. Maybe I've got the sql alchemy syntax wrong? I though insert().values took in iterables for row by row inserts...
Chris
On Sun, Aug 30, 2015 at 3:01 PM, Christophe Pettus <xof@thebuild.com> wrote:
On Aug 30, 2015, at 11:59 AM, Christopher Brooks <brooksch@umich.edu> wrote:
> [SQL: 'INSERT INTO qualtrics_dim (k, v) VALUES (%(k)s, %(v)s)'] [parameters: {'v': ['aa', 'bb'], 'k': ['a', 'b']}]
The values you've written there are two arrays of TEXT values, but the columns aren't declared as TEXT or VARCHAR arrays, but as single VARCHAR values. Are you trying to insert two rows of the form 'aa', 'a' and 'bb', 'b'?
--
-- Christophe Pettus
xof@thebuild.com
Christopher Brooks, PhD
Research Fellow
E-Mail: brooksch@umich.edu
Mail:School of Information
University of Michigan
4322 North Quad
105 S. State St.
Ann Arbor, MI 48109-1285
On 08/30/2015 12:04 PM, Christopher Brooks wrote: > Yes, this is what I was aiming to do. Maybe I've got the sql alchemy > syntax wrong? I though insert().values took in iterables for row by row > inserts... > Think you want to take a look here: http://docs.sqlalchemy.org/en/rel_1_0/core/dml.html#sqlalchemy.sql.expression.Insert.values in particular: The Insert construct also supports multiply-rendered VALUES construct, for those backends which support this SQL syntax (SQLite, Postgresql, MySQL). This mode is indicated by passing a list of one or more dictionaries/tuples: users.insert().values([ {"name": "some name"}, {"name": "some other name"}, {"name": "yet another name"}, ]) > Chris > > On Sun, Aug 30, 2015 at 3:01 PM, Christophe Pettus <xof@thebuild.com > <mailto:xof@thebuild.com>> wrote: > > > On Aug 30, 2015, at 11:59 AM, Christopher Brooks <brooksch@umich.edu > <mailto:brooksch@umich.edu>> wrote: > > [SQL: 'INSERT INTO qualtrics_dim (k, v) VALUES (%(k)s, %(v)s)'] [parameters: {'v': ['aa', 'bb'], 'k': ['a', 'b']}] > > The values you've written there are two arrays of TEXT values, but > the columns aren't declared as TEXT or VARCHAR arrays, but as single > VARCHAR values. Are you trying to insert two rows of the form 'aa', > 'a' and 'bb', 'b'? > > -- > -- Christophe Pettus > xof@thebuild.com <mailto:xof@thebuild.com> > > > > > -- > Christopher Brooks, PhD > Research Fellow > > E-Mail: brooksch@umich.edu <mailto:brooksch@umich.edu> > Web: http://www.si.umich.edu/people/christopher-brooks > > Mail:School of Information > University of Michigan > 4322 North Quad > 105 S. State St. > Ann Arbor, MI 48109-1285 -- Adrian Klaver adrian.klaver@aklaver.com
Thanks for this Adrian and Christophe, this was the issue!
Regards,
Chris
On Sun, Aug 30, 2015 at 3:49 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 08/30/2015 12:04 PM, Christopher Brooks wrote:Yes, this is what I was aiming to do. Maybe I've got the sql alchemy
syntax wrong? I though insert().values took in iterables for row by row
inserts...
Think you want to take a look here:
http://docs.sqlalchemy.org/en/rel_1_0/core/dml.html#sqlalchemy.sql.expression.Insert.values
in particular:
The Insert construct also supports multiply-rendered VALUES construct, for those backends which support this SQL syntax (SQLite, Postgresql, MySQL). This mode is indicated by passing a list of one or more dictionaries/tuples:
users.insert().values([
{"name": "some name"},
{"name": "some other name"},
{"name": "yet another name"},
])Chris
On Sun, Aug 30, 2015 at 3:01 PM, Christophe Pettus <xof@thebuild.com
<mailto:xof@thebuild.com>> wrote:
On Aug 30, 2015, at 11:59 AM, Christopher Brooks <brooksch@umich.edu
<mailto:brooksch@umich.edu>> wrote:
> [SQL: 'INSERT INTO qualtrics_dim (k, v) VALUES (%(k)s, %(v)s)'] [parameters: {'v': ['aa', 'bb'], 'k': ['a', 'b']}]
The values you've written there are two arrays of TEXT values, but
the columns aren't declared as TEXT or VARCHAR arrays, but as single
VARCHAR values. Are you trying to insert two rows of the form 'aa',
'a' and 'bb', 'b'?
--
-- Christophe Pettus
xof@thebuild.com <mailto:xof@thebuild.com>
--
Christopher Brooks, PhD
Research Fellow
E-Mail: brooksch@umich.edu <mailto:brooksch@umich.edu>
Web: http://www.si.umich.edu/people/christopher-brooks
Mail:School of Information
University of Michigan
4322 North Quad
105 S. State St.
Ann Arbor, MI 48109-1285
--
Adrian Klaver
adrian.klaver@aklaver.com
Christopher Brooks, PhD
Research Fellow
E-Mail: brooksch@umich.edu
Mail:School of Information
University of Michigan
4322 North Quad
105 S. State St.
Ann Arbor, MI 48109-1285