Операционная ошибка SQLAlchemy
Я пытаюсь заполнить свою базу данных файлом puppypopulator.py, представленным на уроке. Но проблема, с которой я сталкиваюсь, заключается в том, что я запрашиваю его у сеанса в оболочке, чтобы проверить, правильно ли я заполнил свою базу данных, но, к моему разочарованию, я нахожу эту ошибку:-
Python 2.7.12 (default, Nov 19 2016, 06:48:10)
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more in formation.
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import sessionmaker
>>> from database_setup_puppy import Base, Puppy, Shelter
>>> engine = create_engine('sqlite:///puppies.db
File "<stdin>", line 1
engine = create_engine('sqlite:///puppies.db
^
SyntaxError: EOL while scanning string literal
>>> engine = create_engine('sqlite:///puppies.db')
>>> Base.metadata.bind = engine
>>> DBSession = sessionmaker(bind = engine)
>>> session = DBSession()
>>> session.query(Puppy).all()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/or m/query.py", line 2703, in all
return list(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/or m/query.py", line 2855, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/or m/query.py", line 2878, in _execute_and_instances
result = conn.execute(querycontext.statement, self._para ms)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sq l/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multipara ms, params)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 1189, in _execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 1402, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ut il/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cau se)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 1182, in _execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: puppy.dateOfBirth [SQL: u'SELECT puppy.id AS puppy_id, puppy.name AS puppy_name, puppy.gender AS puppy_g ender, puppy."dateOfBirth" AS "puppy_dateOfBirth", puppy.pic ture AS puppy_picture, puppy.shelter_id AS puppy_shelter_id, puppy.weight AS puppy_weight \nFROM puppy']
>>> session.query(Shelter).all()
[]
>>> session.query(Puppy).all()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/or m/query.py", line 2703, in all
return list(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/or m/query.py", line 2855, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/or m/query.py", line 2878, in _execute_and_instances
result = conn.execute(querycontext.statement, self._para ms)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sq l/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multipara ms, params)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 1189, in _execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 1402, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ut il/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cau se)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/base.py", line 1182, in _execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/en gine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: puppy.dateOfBirth [SQL: u'SELECT puppy.id AS puppy_id, puppy.name AS puppy_name, puppy.gender AS puppy_g ender, puppy."dateOfBirth" AS "puppy_dateOfBirth", puppy.pic ture AS puppy_picture, puppy.shelter_id AS puppy_shelter_id, puppy.weight AS puppy_weight \nFROM puppy']
>>>
Мой код для database_setup_puppy.py, используемый в приведенном выше импорте оболочки, выглядит так:
from sqlalchemy import Column, ForeignKey, Integer, String, Date, Numeric
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
class Shelter(Base):
__tablename__ = 'shelter'
id = Column(Integer, primary_key = True)
name =Column(String(80), nullable = False)
address = Column(String(250))
city = Column(String(80))
state = Column(String(20))
zipCode = Column(String(10))
website = Column(String)
class Puppy(Base):
__tablename__ = 'puppy'
id = Column(Integer, primary_key=True)
name = Column(String(250), nullable=False)
gender = Column(String(6), nullable = False)
dateOfBirth = Column(Date)
picture = Column(String)
shelter_id = Column(Integer, ForeignKey('shelter.id'))
shelter = relationship(Shelter)
weight = Column(Numeric(10))
И модуль щенка популяции это:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from puppies import Base, Shelter, Puppy
#from flask.ext.sqlalchemy import SQLAlchemy
from random import randint
import datetime
import random
engine = create_engine('sqlite:///puppyshelter.db')
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()
#Add Shelters
shelter1 = Shelter(name = "Oakland Animal Services", address = "1101 29th Ave", city = "Oakland", state = "California", zipCode = "94601", website = "oaklandanimalservices.org")
session.add(shelter1)
shelter2 = Shelter(name = "San Francisco SPCA Mission Adoption Center", address="250 Florida St", city="San Francisco", state="California", zipCode = "94103", website = "sfspca.org")
session.add(shelter2)
shelter3 = Shelter(name = "Wonder Dog Rescue", address= "2926 16th Street", city = "San Francisco", state = "California" , zipCode = "94103", website = "http://wonderdogrescue.org")
session.add(shelter3)
shelter4 = Shelter(name = "Humane Society of Alameda", address = "PO Box 1571" ,city = "Alameda" ,state = "California", zipCode = "94501", website = "hsalameda.org")
session.add(shelter4)
shelter5 = Shelter(name = "Palo Alto Humane Society" ,address = "1149 Chestnut St." ,city = "Menlo Park", state = "California" ,zipCode = "94025", website = "paloaltohumane.org")
session.add(shelter5)
#Add Puppies
male_names = ["Bailey", "Max", "Charlie", "Buddy","Rocky","Jake", "Jack", "Toby", "Cody", "Buster", "Duke", "Cooper", "Riley", "Harley", "Bear", "Tucker", "Murphy", "Lucky", "Oliver", "Sam", "Oscar", "Teddy", "Winston", "Sammy", "Rusty", "Shadow", "Gizmo", "Bentley", "Zeus", "Jackson", "Baxter", "Bandit", "Gus", "Samson", "Milo", "Rudy", "Louie", "Hunter", "Casey", "Rocco", "Sparky", "Joey", "Bruno", "Beau", "Dakota", "Maximus", "Romeo", "Boomer", "Luke", "Henry"]
female_names = ['Bella', 'Lucy', 'Molly', 'Daisy', 'Maggie', 'Sophie', 'Sadie', 'Chloe', 'Bailey', 'Lola', 'Zoe', 'Abby', 'Ginger', 'Roxy', 'Gracie', 'Coco', 'Sasha', 'Lily', 'Angel', 'Princess','Emma', 'Annie', 'Rosie', 'Ruby', 'Lady', 'Missy', 'Lilly', 'Mia', 'Katie', 'Zoey', 'Madison', 'Stella', 'Penny', 'Belle', 'Casey', 'Samantha', 'Holly', 'Lexi', 'Lulu', 'Brandy', 'Jasmine', 'Shelby', 'Sandy', 'Roxie', 'Pepper', 'Heidi', 'Luna', 'Dixie', 'Honey', 'Dakota']
puppy_images = ["http://pixabay.com/get/da0c8c7e4aa09ba3a353/1433170694/dog-785193_1280.jpg?direct", "http://pixabay.com/get/6540c0052781e8d21783/1433170742/dog-280332_1280.jpg?direct","http://pixabay.com/get/8f62ce526ed56cd16e57/1433170768/pug-690566_1280.jpg?direct","http://pixabay.com/get/be6ebb661e44f929e04e/1433170798/pet-423398_1280.jpg?direct","http://pixabay.com/static/uploads/photo/2010/12/13/10/20/beagle-puppy-2681_640.jpg","http://pixabay.com/get/4b1799cb4e3f03684b69/1433170894/dog-589002_1280.jpg?direct","http://pixabay.com/get/3157a0395f9959b7a000/1433170921/puppy-384647_1280.jpg?direct","http://pixabay.com/get/2a11ff73f38324166ac6/1433170950/puppy-742620_1280.jpg?direct","http://pixabay.com/get/7dcd78e779f8110ca876/1433170979/dog-710013_1280.jpg?direct","http://pixabay.com/get/31d494632fa1c64a7225/1433171005/dog-668940_1280.jpg?direct"]
#This method will make a random age for each puppy between 0-18 months(approx.) old from the day the algorithm was run.
def CreateRandomAge():
today = datetime.date.today()
days_old = randint(0,540)
birthday = today - datetime.timedelta(days = days_old)
return birthday
#This method will create a random weight between 1.0-40.0 pounds (or whatever unit of measure you prefer)
def CreateRandomWeight():
return random.uniform(1.0, 40.0)
for i,x in enumerate(male_names):
new_puppy = Puppy(name = x, gender = "male", dateOfBirth = CreateRandomAge(),picture=random.choice(puppy_images) ,shelter_id=randint(1,5), weight= CreateRandomWeight())
session.add(new_puppy)
session.commit()
for i,x in enumerate(female_names):
new_puppy = Puppy(name = x, gender = "female", dateOfBirth = CreateRandomAge(),picture=random.choice(puppy_images),shelter_id=randint(1,5), weight= CreateRandomWeight())
session.add(new_puppy)
session.commit()
Я новичок в мире программирования, и все это очень сложно для меня. Я просто не могу найти, где я иду не так. Спасибо за ваше время прохождения таких длинных кодов. Любая помощь высоко ценится.
1 ответ
Ни в примере консоли, ни в модуле populator не создаются таблицы базы данных (обратите внимание, что каждое определение механизма имеет другое имя базы данных). При запросе несуществующей таблицы SQLite выдает ошибку, подобную этой*:
>>> import sqlalchemy as sa
>>>
>>> sqe = sa.create_engine('sqlite://')
>>> # Declare a table but don't create it.
>>> tb = sa.Table('t', sa.MetaData(), sa.Column('id', sa.Integer(), primary_key=True))
>>> with sqe.connect() as conn:
... res = conn.execute(sa.select(tb))
...
Traceback (most recent call last):
...
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: t
[SQL: SELECT t.id
FROM t]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Решение состоит в том, чтобы создать таблицы, например, если вы используете ORM:
Base.metadata.create_all(engine)
Это необходимо сделать после определения классов модели ORM и до отправки каких-либо запросов.
* В этом примере для краткости используется ядро SQLAlchemy, но ошибка одинакова независимо от того, используется ли ядро или уровень ORM.