[sqlalchemy] oracle+pyodbc ?
Hi Unfortunately i have to work with an old Oracle 9i database. I've managed to setup cx_oracle 4.4.1 with python2.7 for oracle 9i, but i need to work with python3 and cx_oracle doesn't support oracle 9i for python3.x. So i tried pyodbc and i can successfully connect to oracle 9i with it. Now i want to know is there any way to use sqlalchemy with pyodbc in order to connect to oracle 9i ? I've tried *oracle+pyodbc://me:pass@127.0.0.1/sid* but i got this error: sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:oracle.pyodbc Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] load Pickled metadata with deferred reflection in SQLAlchemy 0.7 (Postgresql 9.1)
Hello Can anyone look at my question which I asked at stackoverflow: http://stackoverflow.com/questions/30256605/use-deferred-reflection-in-sqlalchemy-0-7-with-cached-metadata? The main question is: how to load correctly pickled metadata to avoid overheads due to tables reflection in SQLAlchemy 0.7.9? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?
Hi, I've built a postgresql function that takes as input a row from a table, and returns the sum of a particular column (of type array) between two specified indices. Here is how I've defined my function CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube, index1 integer, index2 integer) RETURNS numeric LANGUAGE plpgsql STABLE AS $$ DECLARE result numeric; BEGIN select sum(f) from unnest(cube.specres[index1:index2]) as f into result; return result; END; $$; and here is how it works in psql. select c.pk from datadb.cube as c where(specres_sum_segment(c,2000,2005) 12000); This works and returns the cube entries where this condition is true. Now I'm trying to call this function with an SQLalchemy query. I've mapped a DeclarativeMeta class called Cube to my datadb.cube table, but when I try to run my session query I'm getting an error. My sqlalchemy session query is session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)== 12000).all() but I get the error ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'DeclarativeMeta' What is the right syntax to use when passing a mapped SQLalchemy class into a function so postgresql will understand it? I'm using SQLalchemy 1.0.0 and PostgreSQL 9.3. Any help would be appreciated. Thanks. Cheers, Brian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?
On 5/20/15 12:09 PM, Brian Cherinka wrote: Hi, I've built a postgresql function that takes as input a row from a table, and returns the sum of a particular column (of type array) between two specified indices. Here is how I've defined my function CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube, index1 integer, index2 integer) RETURNS numeric LANGUAGE plpgsql STABLE AS $$ DECLARE result numeric; BEGIN select sum(f) from unnest(cube.specres[index1:index2]) as f into result; return result; END; $$; and here is how it works in psql. select c.pk from datadb.cube as c where(specres_sum_segment(c,2000,2005) 12000); This works and returns the cube entries where this condition is true. Now I'm trying to call this function with an SQLalchemy query. I've mapped a DeclarativeMeta class called Cube to my datadb.cube table, but when I try to run my session query I'm getting an error. My sqlalchemy session query is session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)== 12000).all() but I get the error ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'DeclarativeMeta' sounds like your use of declarative is incorrect, please share the means by which the Cube class is declared as well as its base. What is the right syntax to use when passing a mapped SQLalchemy class into a function so postgresql will understand it? I'm using SQLalchemy 1.0.0 and PostgreSQL 9.3. Any help would be appreciated. Thanks. Cheers, Brian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] MariaDB and SQLAlchemy
Michael,
Thanks for the response. The configuration of the username was indeed the
problem!
Meg
On Tuesday, May 19, 2015 at 5:04:21 PM UTC-6, Michael Bayer wrote:
On 5/19/15 6:53 PM, Margaret Tilton wrote:
Hello,
If there is documentation on this that I missed, please let me know.
I have code that worked fine when I was using a MySQL database. My
organization has switched to using MariaDB, which I was told was virtually
identical to MySQL. It seems can connect to a MariaDB db using the
following statement, which doesn't generate any errors:
engine =
create_engine('mysql+pymysql://[user]:[password@[server]/[db]',
pool_recycle=3600)
Then the script tries to execute a simple select statement that worked
fine on MySQL:
check_for_table = SELECT * FROM tb_metadata
table_result = session.execute(check_for_table)
At this point the script throws an error
(sqlalchemy.exc.OperationalError: (OperationalError) (1045, uAccess
denied for user XXX)
that issue is not within SQLAlchemy, it has to do with the configuration
of the username which you are connecting with as well as the host
configuration. You should try testing first with the mysql command
line client.Take a look at
https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/
for configuration instructions.
Any ideas? I would like to keep using SQLAlchemy if possible.
Thank you,
Meg
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: oracle+pyodbc ?
I just tried cx_oracle 5.1.3 with python 3.4: C:\temp\cx_Oracle-5.1.3python setup.py build running build running build_ext building 'cx_Oracle' extension creating build creating build\temp.win32-3.4-9i creating build\temp.win32-3.4-9i\Release C:\Program Files\Microsoft Visual Studio 10.0\VC\BIN\cl.exe /c /nologo /Ox /MD /W3 /GS- /DNDE BUG -IC:\oracle\ora90\oci\include -IC:\oracle\ora90\rdbms\demo -IC:\Python34\include -IC:\Pyt hon34\include /Tccx_Oracle.c /Fobuild\temp.win32-3.4-9i\Release\cx_Oracle.obj -DBUILD_VERSION =5.1.3 cx_Oracle.c cx_Oracle.c(27) : fatal error C1189: #error : Unsupported version of OCI. error: command 'C:\\Program Files\\Microsoft Visual Studio 10.0\\VC\\BIN\\cl.exe' failed with exit status 2 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?
On 5/20/15 12:09 PM, Brian Cherinka wrote: Hi, I've built a postgresql function that takes as input a row from a table, and returns the sum of a particular column (of type array) between two specified indices. Here is how I've defined my function CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube, index1 integer, index2 integer) RETURNS numeric LANGUAGE plpgsql STABLE AS $$ DECLARE result numeric; BEGIN select sum(f) from unnest(cube.specres[index1:index2]) as f into result; return result; END; $$; and here is how it works in psql. select c.pk from datadb.cube as c where(specres_sum_segment(c,2000,2005) 12000); This works and returns the cube entries where this condition is true. Now I'm trying to call this function with an SQLalchemy query. I've mapped a DeclarativeMeta class called Cube to my datadb.cube table, but when I try to run my session query I'm getting an error. My sqlalchemy session query is session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)== 12000).all() this is the problem, you can't pass a declarative class or Table object as an argument to a function.It is extremely unusual that Postgresql allows the name of a table or alias to be an argument to a function, so to support this you need to hardwire the table or alias name in use to make this happen as a SQL expression, as in: print( session.query(Cube).filter( func.specres_sum_segment(literal_column(cube), 2000, 2005) == 12000 ).all() ) but I get the error ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'DeclarativeMeta' What is the right syntax to use when passing a mapped SQLalchemy class into a function so postgresql will understand it? I'm using SQLalchemy 1.0.0 and PostgreSQL 9.3. Any help would be appreciated. Thanks. Cheers, Brian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?
Hi Michael,
Here is the beginning of my Cube class in SQLalchemy. It also has a bunch
of properties and methods I'm not printing here, to keep it short
class Cube(Base,ArrayOps):
__tablename__ = 'cube'
__table_args__ = {'autoload' : True, 'schema' : 'mangadatadb',
'extend_existing':True}
specres = deferred(Column(ARRAY(Float)))
def __repr__(self):
return 'Cube (pk={0}, plate={1}, ifudesign={2}, tag={3})'.format(self.pk,
self.plate, self.ifu.name,self.pipelineInfo.version.version)
Here is the chain (going backwards) that produces my Base
Base = db.Base
db = DatabaseConnection()
and here is my DatabaseConnection class
class DatabaseConnection(object):
_singletons = dict()
def __new__(cls, database_connection_string=None, expire_on_commit=True):
This overrides the object's usual creation mechanism.
if not cls in cls._singletons:
assert database_connection_string is not None, A database connection
string must be specified!
cls._singletons[cls] = object.__new__(cls)
#
# This is the custom initialization
#
me = cls._singletons[cls] # just for convenience (think self)
me.database_connection_string = database_connection_string
# change 'echo' to print each SQL query (for debugging/optimizing/the
curious)
me.engine = create_engine(me.database_connection_string, echo=False)
me.metadata = MetaData()
me.metadata.bind = me.engine
me.Base = declarative_base(bind=me.engine)
me.Session = scoped_session(sessionmaker(bind=me.engine, autocommit=True,
expire_on_commit=expire_on_commit))
Cheers, Brian
On Wednesday, May 20, 2015 at 12:51:36 PM UTC-4, Michael Bayer wrote:
On 5/20/15 12:09 PM, Brian Cherinka wrote:
Hi,
I've built a postgresql function that takes as input a row from a table,
and returns the sum of a particular column (of type array) between two
specified indices. Here is how I've defined my function
CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube, index1
integer, index2 integer) RETURNS numeric
LANGUAGE plpgsql STABLE
AS $$
DECLARE result numeric;
BEGIN
select sum(f) from unnest(cube.specres[index1:index2]) as f into result;
return result;
END; $$;
and here is how it works in psql.
select c.pk from datadb.cube as c where(specres_sum_segment(c,2000,2005)
12000);
This works and returns the cube entries where this condition is true.
Now I'm trying to call this function with an SQLalchemy query. I've
mapped a DeclarativeMeta class called Cube to my datadb.cube table, but
when I try to run my session query I'm getting an error.
My sqlalchemy session query is
session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)==
12000).all()
but I get the error
ProgrammingError: (psycopg2.ProgrammingError) can't adapt type
'DeclarativeMeta'
sounds like your use of declarative is incorrect, please share the means
by which the Cube class is declared as well as its base.
What is the right syntax to use when passing a mapped SQLalchemy class
into a function so postgresql will understand it? I'm using SQLalchemy
1.0.0 and PostgreSQL 9.3. Any help would be appreciated. Thanks.
Cheers, Brian
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] oracle+pyodbc ?
This is what i get when i've tried to compile *cx_oracle 4.4.1* with *python 3.4* on XP machine with oracle 9i installed: *C:\temp\cx_Oracle-4.4.1python setup.py buildrunning buildrunning build_extbuilding 'cx_Oracle' extensionC:\Program Files\Microsoft Visual Studio 10.0\VC\BIN\cl.exe /c /nologo /Ox /MD /W3 /GS- /DNDEBUG -IC:\oracle\ora90\oci\include -IC:\oracle\ora90\rdbms\demo -IC:\Python34\include -IC:\Python34\include /Tccx_Oracle.c /Fobuild\temp.win32-3.4-9i\Release\cx_Oracle.obj -DBUILD_VERSION=4.4.1cx_Oracle.cc:\temp\cx_oracle-4.4.1\Environment.c(35) : warning C4047: 'initializing' : 'Py_ssize_t' differs in levels of indirection from 'char [18]'c:\temp\cx_oracle-4.4.1\Environment.c(38) : warning C4113: 'destructor' differs in parameterlists from 'printfunc'c:\temp\cx_oracle-4.4.1\Environment.c(38) : warning C4133: 'initializing' : incompatible types - from 'destructor' to 'printfunc'c:\temp\cx_oracle-4.4.1\Environment.c(53) : warning C4047: 'initializing' : 'const char *' differs in levels of indirection from 'unsigned long'c:\temp\cx_oracle-4.4.1\Error.c(41) : warning C4047: 'initializing' : 'Py_ssize_t' differs in levels of indirection from 'char [17]'c:\temp\cx_oracle-4.4.1\Error.c(44) : warning C4113: 'destructor' differs in parameter listsfrom 'printfunc'c:\temp\cx_oracle-4.4.1\Error.c(44) : warning C4133: 'initializing' : incompatible types - from 'destructor' to 'printfunc'c:\temp\cx_oracle-4.4.1\Error.c(55) : warning C4113: 'reprfunc' differs in parameter lists from 'getattrofunc'c:\temp\cx_oracle-4.4.1\Error.c(59) : warning C4047: 'initializing' : 'const char *' differsin levels of indirection from 'unsigned long'c:\temp\cx_oracle-4.4.1\Error.c(68) : warning C4133: 'initializing' : incompatible types - from 'PyMemberDef *' to 'PyGetSetDef *'c:\temp\cx_oracle-4.4.1\Error.c(119) : error C2039: 'ob_type' : is not a member of 'udt_Error'c:\temp\cx_oracle-4.4.1\Error.c(9) : see declaration of 'udt_Error'c:\temp\cx_oracle-4.4.1\Error.c(130) : warning C4013: 'PyString_FromString' undefined; assuming extern returning intc:\temp\cx_oracle-4.4.1\Error.c(130) : warning C4047: 'return' : 'PyObject *' differs in levels of indirection from 'int'c:\temp\cx_oracle-4.4.1\Connection.c(145) : warning C4047: 'initializing' : 'Py_ssize_t' differs in levels of indirection from 'char [21]'c:\temp\cx_oracle-4.4.1\Connection.c(148) : warning C4113: 'destructor' differs in parameterlists from 'printfunc'c:\temp\cx_oracle-4.4.1\Connection.c(148) : warning C4133: 'initializing' : incompatible types - from 'destructor' to 'printfunc'c:\temp\cx_oracle-4.4.1\Connection.c(153) : warning C4047: 'initializing' : 'PyNumberMethods*' differs in levels of indirection from 'reprfunc'c:\temp\cx_oracle-4.4.1\Connection.c(163) : warning C4047: 'initializing' : 'const char *' differs in levels of indirection from 'unsigned long'c:\temp\cx_oracle-4.4.1\Connection.c(172) : warning C4133: 'initializing' : incompatible types - from 'PyMethodDef *' to 'PyMemberDef *'c:\temp\cx_oracle-4.4.1\Connection.c(173) : warning C4133: 'initializing' : incompatible types - from 'PyMemberDef *' to 'PyGetSetDef *'c:\temp\cx_oracle-4.4.1\Connection.c(174) : warning C4133: 'initializing' : incompatible types - from 'PyGetSetDef *' to '_typeobject *'c:\temp\cx_oracle-4.4.1\Connection.c(180) : warning C4113: 'initproc' differs in parameter lists from 'allocfunc'c:\temp\cx_oracle-4.4.1\Connection.c(180) : warning C4047: 'initializing' : 'allocfunc' differs in levels of indirection from 'initproc'c:\temp\cx_oracle-4.4.1\Connection.c(182) : warning C4113: 'newfunc' differs in parameter lists from 'freefunc'c:\temp\cx_oracle-4.4.1\Connection.c(182) : warning C4047: 'initializing' : 'freefunc' differs in levels of indirection from 'newfunc'c:\temp\cx_oracle-4.4.1\Connection.c(398) : warning C4013: 'PyString_GET_SIZE' undefined; assuming extern returning intc:\temp\cx_oracle-4.4.1\Connection.c(401) : warning C4013: 'PyString_AS_STRING' undefined; assuming extern returning intc:\temp\cx_oracle-4.4.1\Cursor.c(135) : warning C4047: 'initializing' : 'Py_ssize_t' differsin levels of indirection from 'char [13]'c:\temp\cx_oracle-4.4.1\Cursor.c(138) : warning C4113: 'destructor' differs in parameter lists from 'printfunc'c:\temp\cx_oracle-4.4.1\Cursor.c(138) : warning C4133: 'initializing' : incompatible types -from 'destructor' to
Re: [sqlalchemy] Re: oracle+pyodbc ?
On 5/20/15 2:24 PM, Mehdi wrote: I just tried cx_oracle 5.1.3 with python 3.4: | C:\temp\cx_Oracle-5.1.3python setup.py build running build running build_ext building 'cx_Oracle'extension creating build creating build\temp.win32-3.4-9i creating build\temp.win32-3.4-9i\Release C:\ProgramFiles\MicrosoftVisualStudio10.0\VC\BIN\cl.exe /c /nologo /Ox/MD /W3 /GS-/DNDE BUG -IC:\oracle\ora90\oci\include -IC:\oracle\ora90\rdbms\demo -IC:\Python34\include -IC:\Pyt hon34\include /Tccx_Oracle.c /Fobuild\temp.win32-3.4-9i\Release\cx_Oracle.obj -DBUILD_VERSION =5.1.3 cx_Oracle.c cx_Oracle.c(27):fatal error C1189:#error:Unsupportedversion of OCI. error:command 'C:\\Program Files\\Microsoft Visual Studio 10.0\\VC\\BIN\\cl.exe'failed with exitstatus 2 | You should install newer OCI libraries to use for the client install, they should be compatible with an older server version. Email the cx_oracle mailing list at http://lists.sourceforge.net/lists/listinfo/cx-oracle-users ; support information for cx_oracle can be found at https://bitbucket.org/anthony_tuininga/cx_oracle. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] oracle+pyodbc ?
On 5/20/15 2:18 PM, Mehdi wrote: This is what i get when i've tried to compile *cx_oracle 4.4.1* with *python 3.4* on XP machine with oracle 9i installed: 4.4.1 ? That is an ancient version that does not support Python 3, and SQLAlchemy doesn't even have very good support for that old version. You need to be on the 5.x series, install 5.1.3 which you can get at https://pypi.python.org/pypi/cx_Oracle * C:\temp\cx_Oracle-4.4.1python setup.py build * So i'm not sure what should i do? downgrade python version to 3.3 or try to compile latest cx_oracle against oracle 9i? What is significant about Python 3.3 vs. 3.4 ? On Wednesday, May 20, 2015 at 7:09:41 PM UTC+4:30, Michael Bayer wrote: it doesn't? cx_oracle supports Py3K fully, do you have a link for where it fails to work on Oracle 9 ? It's all just OCI and it builds against whatever oracle client libs you have. Send me more detail about the Python3/cx_oracle/9i thing. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Column vs Table constraints for Column(unique=True) in Postgres
Unless you provide a name, the constraint will be anonymously named, so
there is no difference between that and the shortcut.
Provide a name argument to UniqueConstraint:
__table_args__ = (UniqueConstraint('alt_id', name='uq_alt_id'),)
You may also be interested in providing a naming convention to automate
this:
http://docs.sqlalchemy.org/en/latest/core/constraints.html#configuring-constraint-naming-conventions
On Sunday, May 17, 2015 at 7:49:39 PM UTC-5, r...@rosenfeld.to wrote:
Sorry it took my a while to test this, but I didn't see any difference in
the SQL emitted. What did I miss?
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import UniqueConstraint
from sqlalchemy import create_engine
Base = declarative_base()
class Test1(Base):
__tablename__ = 'test_1'
id = Column(Integer, primary_key=True)
alt_id = Column(Integer, nullable=True, default=None, unique=True)
class Test2(Base):
__tablename__ = 'test_2'
id = Column(Integer, primary_key=True)
alt_id = Column(Integer, nullable=True, default=None)
__table_args__ = (UniqueConstraint('alt_id'),)
active_db_url = 'postgres://user:pass@10.10.10.10/db'
engine = create_engine(active_db_url, echo=False)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
And here's what I see in the log:
CREATE TABLE test_2 (
id SERIAL NOT NULL,
alt_id INTEGER,
PRIMARY KEY (id),
UNIQUE (alt_id)
)
REATE TABLE test_1 (
id SERIAL NOT NULL,
alt_id INTEGER,
PRIMARY KEY (id),
UNIQUE (alt_id)
)
On Sunday, May 3, 2015 at 10:23:31 PM UTC-5, Michael Bayer wrote:
sure, use UniqueConstraint directly. It's better to use that than the
unique=True flag in any case.
On 5/3/15 10:29 PM, r...@rosenfeld.to wrote:
Is there a way to control whether DDL emitted by SQLAlchemy uses a
column and/or table constraint for uniqueness?
It seems the following
class Part(Base):
__tablename__ = 'part'
third_party_id = Column(Integer, nullable=True, default=None, unique=
True)
emits a table constraint
CREATE TABLE part (
third_party_id INTEGER,
CONSTRAINT uq_part_third_party_id UNIQUE (third_party_id)
)
Is it possible to emit the following with a column constraint instead?
CREATE TABLE part (
third_party_id INTEGER CONSTRAINT uq_part_third_party_id UNIQUE
)
Thanks
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] oracle+pyodbc ?
On 5/20/15 3:45 AM, Mehdi wrote: Hi Unfortunately i have to work with an old Oracle 9i database. I've managed to setup cx_oracle 4.4.1 with python2.7 for oracle 9i, but i need to work with python3 and cx_oracle doesn't support oracle 9i for python3.x. it doesn't? cx_oracle supports Py3K fully, do you have a link for where it fails to work on Oracle 9 ? It's all just OCI and it builds against whatever oracle client libs you have. So i tried pyodbc and i can successfully connect to oracle 9i with it. Now i want to know is there any way to use sqlalchemy with pyodbc in order to connect to oracle 9i ? I've tried /oracle+pyodbc://me:pass@127.0.0.1/sid/ but i got this error: | sqlalchemy.exc.NoSuchModuleError:Can't load plugin: sqlalchemy.dialects:oracle.pyodbc | we don't have a dialect for that right now. You can make one, though it might have lots of issues as pyodbc is a difficult driver to work with in general and might not have support for many features of the Oracle database, typically this regards poor handling of datatypes (things like unicode, etc.). I've attached the most rudimentary dialect possible for this. If you were to import oracle_pyodbc, you would then have available an engine URL like create_engine(oracle+pyodbc://user:pass@DSN). I don't have an environment set up to interface ODBC to Oracle here and while this dialect should be able to make a successful connection, it might fail very quickly afterwards. Send me more detail about the Python3/cx_oracle/9i thing. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. from sqlalchemy.dialects.oracle.base import OracleDialect, \ OracleExecutionContext from sqlalchemy.connectors.pyodbc import PyODBCConnector class OracleExecutionContext_pyodbc(OracleExecutionContext): pass class OracleDialect_pyodbc(PyODBCConnector, OracleDialect): execution_ctx_cls = OracleExecutionContext_pyodbc pyodbc_driver_name = Oracle dialect = OracleDialect_pyodbc from sqlalchemy.dialects import registry registry.register(oracle.pyodbc, oracle_pyodbc, OracleDialect_pyodbc)
Re: [sqlalchemy] load Pickled metadata with deferred reflection in SQLAlchemy 0.7 (Postgresql 9.1)
On 5/20/15 9:54 AM, m1st9r...@gmail.com wrote: Hello Can anyone look at my question which I asked at stackoverflow: http://stackoverflow.com/questions/30256605/use-deferred-reflection-in-sqlalchemy-0-7-with-cached-metadata? The main question is: how to load correctly pickled metadata to avoid overheads due to tables reflection in SQLAlchemy 0.7.9? the issue regards foreign key constraints not being restored, but the nature of it is unknown. Please work up a succinct, single-file test case; ensure it still fails on modern versions, e.g. 0.9, 1.0 etc. then send it along. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
