databaseapi.rst 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568
  1. .. _database-api:
  2. Database API
  3. ============
  4. South ships with a full database-agnostic API for performing schema changes
  5. on databases, much like Django's ORM provides data manipulation support.
  6. Currently, South supports:
  7. - PostgreSQL
  8. - MySQL
  9. - SQLite
  10. - Microsoft SQL Server (beta support)
  11. - Oracle (alpha support)
  12. .. _accessing-the-api:
  13. Accessing The API
  14. -----------------
  15. South automatically exposes the correct set of database API operations as
  16. ``south.db.db``; it detects which database backend you're using from your
  17. Django settings file. It's usually imported using::
  18. from south.db import db
  19. If you're using multiple database support (Django 1.2 and higher),
  20. there's a corresponding ``south.db.dbs`` dictionary
  21. which contains a DatabaseOperations object (the object which has the methods
  22. defined above) for each database alias in your configuration file::
  23. from south.db import dbs
  24. dbs['users'].create_table(...)
  25. You can tell which backend you're talking to inside of a migration by examining
  26. ``db.backend_name`` - it will be one of ``postgres``, ``mysql``, ``sqlite3``,
  27. ``pyodbc`` or ``oracle``.
  28. Database-Specific Issues
  29. ------------------------
  30. South provides a large amount of features, and not all features are supported by
  31. all database backends.
  32. - PostgreSQL supports all of the South features; if you're unsure which database
  33. engine to pick, it's the one we recommend for migrating on.
  34. - MySQL doesn't have transaction support for schema modification, meaning that
  35. if a migration fails to apply, the database is left in an inconsistent state,
  36. and you'll probably have to manually fix it. South will try and sanity-check
  37. migrations in a dry-run phase, and give you hints of what to do when it
  38. fails, however.
  39. - SQLite doesn't natively support much schema altering at all, but South
  40. has workarounds to allow deletion/altering of columns. Unique indexes are
  41. still unsupported, however; South will silently ignore any such commands.
  42. - SQL Server has been supported for a while, and works in theory, but the
  43. implementation itself may have bugs, as it's a contributed module and isn't
  44. under primary development. Patches and bug reports are welcome.
  45. - Oracle is a new module as of the 0.7 release, and so is very much alpha.
  46. The most common operations work, but others may be missing completely;
  47. we welcome bug reports and patches against it (as with all other modules).
  48. Methods
  49. -------
  50. These are how you perform changes on the database. See :ref:`accessing-the-api`
  51. to see how to get access to the ``db`` object.
  52. .. contents::
  53. :local:
  54. :depth: 1
  55. db.add_column
  56. ^^^^^^^^^^^^^
  57. ::
  58. db.add_column(table_name, field_name, field, keep_default=True)
  59. Adds a column called ``field_name`` to the table ``table_name``, of the type
  60. specified by the field instance field.
  61. If ``keep_default`` is True, then any default value specified on the field will
  62. be added to the database schema for that column permanently. If not, then the
  63. default is only used when adding the column, and then dropped afterwards.
  64. Note that the default value for fields given here is only ever used when
  65. adding the column to a non-empty table; the default used by the ORM in your
  66. application is the one specified on the field in your models.py file, as Django
  67. handles adding default values before the query hits the database.
  68. The only case where having the default stored in the database as well would make
  69. a difference would be where you are interacting with the database from somewhere
  70. else, or Django doesn't know about the added column at all.
  71. Also, note that the name you give for the column is the **field name**, not the
  72. column name - if the field you pass in is a ForeignKey, for example, the
  73. real column name will have _id on the end.
  74. Examples
  75. """"""""
  76. A normal column addition (the column is nullable, so all existing rows will have
  77. it set to NULL)::
  78. db.add_column('core_profile', 'height', models.IntegerField(null=True))
  79. Providing a default value instead, so all current rows will get this value for
  80. 'height'::
  81. db.add_column('core_profile', 'height', models.IntegerField(default=-1))
  82. Same as above, but the default is not left in the database schema::
  83. db.add_column('core_profile', 'height', models.IntegerField(default=-1), keep_default=False)
  84. db.alter_column
  85. ^^^^^^^^^^^^^^^
  86. ::
  87. db.alter_column(table_name, column_name, field, explicit_name=True)
  88. Alters the column ``column_name`` on the table ``table_name`` to match
  89. ``field``. Note that this cannot alter all field attributes; for example, if
  90. you want to make a field ``unique=True``, you should instead use
  91. ``db.add_index`` with ``unique=True``, and if you want to make it a primary
  92. key, you should look into ``db.drop_primary_key`` and ``db.create_primary_key``.
  93. If explicit_name is false, ForeignKey? fields will have _id appended to the end
  94. of the given column name - this lets you address fields as they are represented
  95. in the model itself, rather than as the column name.
  96. Examples
  97. """"""""
  98. A simple change of the length of a VARCHAR column::
  99. # Assume the table was created with name = models.CharField(max_length=50)
  100. db.alter_column('core_nation', 'name', models.CharField(max_length=200))
  101. We can also change it to a compatible field type::
  102. db.alter_column('core_nation', 'name', models.TextField())
  103. If we have a ForeignKey? named 'user', we can address it without the implicit '_id' on the end::
  104. db.alter_column('core_profile', 'user', models.ForeignKey(orm['auth.User'], null=True, blank=True), explicit_name=False)
  105. Or you can specify the same operation with an explicit name::
  106. db.alter_column('core_profile', 'user_id', models.ForeignKey(orm['auth.User'], null=True, blank=True))
  107. db.clear_table
  108. ^^^^^^^^^^^^^^
  109. ::
  110. db.clear_table(table_name)
  111. Deletes all rows from the table (truncation). Never used by South's
  112. autogenerators, but can prove useful if you're writing data migrations.
  113. Examples
  114. """"""""
  115. Clear all cached geocode results, as the schema is changing::
  116. db.clear_table('core_geocoded')
  117. db.add_column('core_geocoded', ...)
  118. db.commit_transaction
  119. ^^^^^^^^^^^^^^^^^^^^^
  120. ::
  121. db.commit_transaction()
  122. Commits the transaction started at a ``db.start_transaction`` call.
  123. db.create_index
  124. ^^^^^^^^^^^^^^^
  125. ::
  126. db.create_index(table_name, column_names, unique=False, db_tablespace='')
  127. Creates an index on the list of columns ``column_names`` on the table
  128. ``table_name``.
  129. By default, the index is simply for speed; if you would like a unique index,
  130. then specify ``unique=True``, although you're better off using
  131. ``db.create_unique`` for that.
  132. ``db_tablespace`` is an Oracle-specific option, and it's likely you won't need
  133. to use it.
  134. Examples
  135. """"""""
  136. Creating an index on the 'name' column::
  137. db.create_index('core_profile', ['name'])
  138. Creating a unique index on the combination of 'name' and 'age' columns::
  139. db.create_index('core_profile', ['name', 'age'], unique=True)
  140. db.create_primary_key
  141. ^^^^^^^^^^^^^^^^^^^^^
  142. ::
  143. db.create_primary_key(table_name, columns)
  144. Creates a primary key spanning the given ``columns`` for the table. Remember,
  145. you can only have one primary key per table; use ``db.delete_primary_key``
  146. first if you already have one.
  147. Examples
  148. """"""""
  149. Swapping from the ``id`` to ``uuid`` as a primary key::
  150. db.delete_primary_key('core_upload')
  151. db.create_primary_key('core_upload', ['uuid'])
  152. Adding a new composite primary key on "first name" and "last name"::
  153. db.create_primary_key('core_people', ['first_name', 'last_name'])
  154. db.create_table
  155. ^^^^^^^^^^^^^^^
  156. ::
  157. db.create_table(table_name, fields)
  158. fields = ((field_name, models.SomeField(somearg=4)), ...)
  159. This call creates a table called *table_name* in the database with the schema
  160. specified by *fields*, which is a tuple of ``(field_name, field_instance)``
  161. tuples.
  162. Note that this call will not automatically add an id column;
  163. you are responsible for doing that.
  164. We recommend you create calls to this function using ``schemamigration``, either
  165. in ``--auto`` mode, or by using ``--add-model``.
  166. Examples
  167. """"""""
  168. A simple table, with one field, name, and the default id column::
  169. db.create_table('core_planet', (
  170. ('id', models.AutoField(primary_key=True)),
  171. ('name', models.CharField(unique=True, max_length=50)),
  172. ))
  173. A more complex table, which uses the ORM Freezer for its foreign keys::
  174. db.create_table('core_nation', (
  175. ('name', models.CharField(max_length=255)),
  176. ('short_name', models.CharField(max_length=50)),
  177. ('slug', models.SlugField(unique=True)),
  178. ('planet', models.ForeignKey(orm.Planet, related_name="nations")),
  179. ('flag', models.ForeignKey(orm.Flag, related_name="nations")),
  180. ('planet_name', models.CharField(max_length=50)),
  181. ('id', models.AutoField(primary_key=True)),
  182. ))
  183. db.create_unique
  184. ^^^^^^^^^^^^^^^^
  185. ::
  186. create_unique(table_name, columns)
  187. Creates a unique index or constraint on the list of columns ``columns`` on the
  188. table ``table_name``.
  189. Examples
  190. """"""""
  191. Declare the pair of fields ``first_name`` and ``last_name`` to be unique::
  192. db.create_unique('core_people', ['first_name', 'last_name'])
  193. db.delete_column
  194. ^^^^^^^^^^^^^^^^
  195. ::
  196. db.delete_column(table_name, column_name)
  197. Deletes the column ``column_name`` from the table ``table_name``.
  198. Examples
  199. """"""""
  200. Delete a column from a table::
  201. db.delete_column('core_nation', 'title')
  202. db.delete_foreign_key
  203. ^^^^^^^^^^^^^^^^^^^^^
  204. ::
  205. delete_foreign_key(table_name, column)
  206. Drops any foreign key constraints on the given column, if the database backend
  207. supported them in the first place.
  208. Examples
  209. """"""""
  210. Remove the foreign key constraint from user_id:
  211. db.delete_foreign_key('core_people', 'user_id')
  212. db.delete_primary_key
  213. ^^^^^^^^^^^^^^^^^^^^^
  214. ::
  215. db.delete_primary_key(table_name)
  216. Deletes the current primary key constraint on the table. Does not remove the
  217. columns the primary key was using.
  218. Examples
  219. """"""""
  220. Swapping from the ``id`` to ``uuid`` as a primary key::
  221. db.delete_primary_key('core_upload')
  222. db.create_primary_key('core_upload', ['uuid'])
  223. db.delete_table
  224. ^^^^^^^^^^^^^^^
  225. ::
  226. db.delete_table(table_name, cascade=True)
  227. Deletes (drops) the named table from the database. If cascade is True, drops any
  228. related constraints as well.
  229. Examples
  230. """"""""
  231. Usual call::
  232. db.delete_table("core_planet")
  233. Not cascading (beware, may fail)::
  234. db.delete_table("core_planet", cascade=False)
  235. db.delete_unique
  236. ^^^^^^^^^^^^^^^^
  237. ::
  238. delete_unique(table_name, columns)
  239. Deletes a unique index or constraint on the list of columns ``columns`` on the
  240. table ``table_name``. The constraint/index. must already exist.
  241. Examples
  242. """"""""
  243. Declare the pair of fields ``first_name`` and ``last_name`` to no longer
  244. be unique::
  245. db.delete_unique('core_people', ['first_name', 'last_name'])
  246. db.execute
  247. ^^^^^^^^^^
  248. ::
  249. db.execute(sql, params=[])
  250. Executes the **single** raw SQL statement ``sql`` on the database; optionally
  251. use params to replace the %s instances in sql (this is the recommended way of
  252. doing parameters, as it escapes them correctly for all databases).
  253. If you want to execute a series of SQL statements instead, use
  254. ``db.execute_many``.
  255. Note that you should avoid using raw SQL wherever possible, as it will break the
  256. database abstraction in many cases. If you want to handle data, consider using
  257. the ORM Freezer, and remember that many operations such as creating indexes and
  258. changing primary keys have functions in the DB layer.
  259. If there's a common operation you'd like to see added to the DB abstraction
  260. layer in South, consider asking on the mailing list or creating a ticket.
  261. Examples
  262. """"""""
  263. VACUUMing a table::
  264. db.execute("VACUUM ANALYZE core_profile")
  265. Updating values (this sort of task should really be done using the frozen ORM)::
  266. db.execute("UPDATE core_profile SET name = %s WHERE name = %s", ["andy", "andrew"])
  267. db.execute_many
  268. ^^^^^^^^^^^^^^^
  269. ::
  270. db.execute_many(sql, regex=r"(?mx) ([^';]* (?:'[^']*'[^';]*)*)", comment_regex=r"(?mx) (?:^\s*$)|(?:--.*$)")
  271. Executes the given multi-statement SQL string ``sql``. The two parameters are
  272. the regular expressions for splitting up statements (``regex``) and removing
  273. comments (``comment_regex``). We recommend you leave these at their default
  274. values, as they work on almost all SQL files.
  275. If you only want to execute a single SQL statement, consider using
  276. ``db.execute``, as it offers parameter escaping, and the regexes sometimes get
  277. the splitting wrong.
  278. Examples
  279. """"""""
  280. Run the PostGIS initialisation file::
  281. db.execute_many(open("/path/to/lwpostgis.sql").read())
  282. db.rename_column
  283. ^^^^^^^^^^^^^^^^
  284. ::
  285. db.rename_column(table_name, column_name, new_column_name)
  286. Renames the column ``column_name`` in table ``table_name`` to
  287. ``new_column_name``.
  288. Examples
  289. """"""""
  290. Simple rename::
  291. db.rename_column('core_nation', 'name', 'title')
  292. db.rename_table
  293. ^^^^^^^^^^^^^^^
  294. ::
  295. db.rename_table(table_name, new_table_name)
  296. Renames the table table_name to the new name new_table_name.
  297. This won't affect what tables your models are looking for, of course;
  298. this is useful, for example, if you've renamed a model
  299. (and don't want to specify the old table name in Meta).
  300. Examples
  301. """"""""
  302. Simple rename::
  303. db.rename_table('core_profile', 'core_userprofile')
  304. db.rollback_transaction
  305. ^^^^^^^^^^^^^^^^^^^^^^^
  306. ::
  307. db.rollback_transaction()
  308. Rolls back the transaction started at a ``db.start_transaction`` call.
  309. db.send_create_signal
  310. ^^^^^^^^^^^^^^^^^^^^^
  311. ::
  312. db.send_create_signal(app_label, model_names)
  313. Sends the post_syncdb signal for the given models ``model_names`` in the app
  314. ``app_label``.
  315. This signal is used by various bits of django internals - such as contenttypes
  316. - to hook new models into themselves, so you should really call it after the
  317. relevant ``db.create_table`` call. ``startmigration`` will add this
  318. automatically for you.
  319. Note that the signals are not sent until the end of the whole migration
  320. sequence, so your handlers will not get called until all migrations are done.
  321. This is so that your handlers can deal with the most recent version of the
  322. model's schema, rather than the one in the migration where the signal is
  323. originally sent.
  324. Examples
  325. """"""""
  326. Sending a signal for the 'Profile' and 'Planet' models in my app 'core'::
  327. db.send_create_signal('core', ['Profile', 'Planet'])
  328. db.start_transaction
  329. ^^^^^^^^^^^^^^^^^^^^
  330. ::
  331. db.start_transaction()
  332. Wraps the following code (until it meets a ``db.rollback_transaction`` or
  333. ``db.commit_transaction`` call) in a transaction.